dataguard部署_dataguard部署两地

作者：爱喝兽奶帝天荒 | 2024-08-13 20:28:49

踩

dataguard部署两地

一、实施案例1：

--oracle9i standby 完整文档

1.设置Primary数据库服务器为归档模式(如果已经归档,则不需再设)

startup mount

conn as sysdba

用sys登录

alter system set log_archive_start=true scope=spfile;(启动arch进程参数设置)

alter system set log_archive_format='ARC%S.%T' scope=spfile;(设置归档文件格式)

alter system set log_archive_dest_1='location=/u03/oracle/backup/arc';(设置归档路径)

shutdown immediate;

startup mount;

alter database archivelog;(启动归档,特别注意,在此时要关监听器,否则报ora 00265错误)
--alter database noarchvielog;(停止归档)
alter database open;(修改启动数据库)

2.创建自动备份数据库

--编辑备份文件rmanhb.sql
run {
allocate channel ch1 type disk maxpiecesize = 2G;
allocate channel ch2 type disk maxpiecesize = 2G;
backup database format '/u02/oracle/%d_DF_%T_%s_%p.bak' plus archivelog
format '/u02/oracle/%d_AF_%T_%s_%p.bak' delete input;
backup current controlfile format '/u02/oracle/%d_CTL_%T_%s_%p.bak';
release channel ch1;
release channel ch2;
}

--编辑执行文件auto.sh
export PATH
export LD_ASSUME_KERNEL=2.4.1
export ORACLE_BASE=/u01/oracle
export ORACLE_HOME=$ORACLE_BASE/product/9.2.0
export ORACLE_SID=orapr
export ORACLE_TERM=xterm
export NLS_LANG=AMERICAN
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib
export LD_LIBRARY_PATH
export PATH=$ORACLE_HOME/bin:$PATH
unset USERNAME
rman target sys/petrel @/u02/oracle/backup/script/rmanhb.sql log /u02/oracle/backup/script/backup.log

--建立任务
crontab -e(参阅crontab相关资料)

3.双机热备实施197.18.114.240(主机),197.18.114.241(备机)

--主机上设置Primary强制Logging(强制写日志,保证standby数据库可以正确恢复.)
startup mount;
alter database force logging;(这一步必须,不能省)

--主机上select file_name from dba_data_files;(查找数据文件路径)

--主机上shutdown immediate;
lsnrctl stop

--备机上mkdir /u02/oracle
mkdir /u02/oracle/oradata
mkdir /u02/oracle/backup
mkdir /u02/oracle/oradata/orapr
mkdir /u02/oracle/backup/arc
mkdir /u01/oracle/admin/orapr/bdump,cdump,udump等
vi /u01/oracle/admin/orapr/bdump/alert_orapr.log
mkdir /u01/oracle/oradata
mkdir /u01/oracle/oradata/orapr(建立备机上的控制文件目录)
--拷贝主机上的数据到备机上

备机上service vsftpd start
主机上ftp open 197.18.114.241
oracle
bin
prompt
cd /u02/oracle/oradata/orapr
lcd /u02/oracle/oradata/orapr
mput *.*
bye
将密码文件orapworapr拷贝到备机上

--生成standby控制文件

主机上startup (监听器停止)
主机上alter database create standby controlfile as '/u02/standby.ctl';(生成standby控制文件)
上传这个standby控制文件到/u01/oracle/oradata/orapr/
cp standby.ctl control01.ctl
cp standby.ctl control02.ctl
cp standby.ctl control03.ctl
rm standby.ctl


--建立tnsnames.ora,listener.ora

tnsnames 主机,备机上可以相同

ORAPR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 197.18.114.240)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orapr)
)
)

ORAST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 197.18.114.241)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = orast)
)
)

listener.ora主机上的设置

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 197.18.100.250)(PORT = 1521))
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ora9i)
(ORACLE_HOME = /u01/oracle/products/9.2.0)
(SID_NAME = ora9i)
)
)

listener.ora备机上的设置
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ora9i)
(ORACLE_HOME = /u01/oracle/products/9.2.0)
(SID_NAME = ora9i)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 197.18.100.249)(PORT = 1521))
)
)
)

--设置Standby的SQLNET.ORA文件

vi sqlnet.ora

添加SQLNET.EXPIRE_TIME=2，该配置表示在Standby由于故障不可用时，Primary将持续检测2分钟，如果仍然不可用，则返回网络连接错误。

--设置参数文件

主机上create pfile='/u02/oraprstand.ora' from spfile;(创建备机参数文件)
修改oraprstand.ora
加入:
*.standby_archive_dest='/archive/cmsnn'
*.fal_server='ORA9I'
*.fal_client='ORA9IS'
*.standby_file_management=auto
*.remote_archive_enable=TRUE

保存后,上传到备机上
备机上create spfile from pfile='/u02/oraprstand.ora'

--启动备机看看,添加日志文件

startup nomount

alter database mount standby database;(注意,如果在这时报错ORA-00205: error in identifying controlfile,可能是我们控制文件路径不对)

添加standby redolog
---select inst_id,group#,status,type,member from gv$logfile;(查看系统日志文件)
---alter database drop logfile group 4;(删除日志文件)
alter database add standby logfile group 6 ('/u02/oracle/oradata/ora9i/stdby_redo06.log') size 50m;
alter database add standby logfile group 7 ('/u02/oracle/oradata/ora9i/stdby_redo07.log') size 50m;
alter database add standby logfile group 8 ('/u02/oracle/oradata/ora9i/stdby_redo08.log') size 50m;

注意事项
在这里,可能有日志组数量的限制报(ERROR at line 1:ORA-01185: logfile group number 7 is invalid),需要我们重建主机控制文件

alter database backup controlfile to trace;(重新测试跟踪文件)

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORA9I" RESETLOGS FORCE LOGGING ARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 8(日志组数,建库时默认为5组)
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 1 (
'/u02/oracle/oradata/ora9i/redo01A.log',
'/u01/oracle/oradata/ora9i/redo01B.log'
) SIZE 50M,
GROUP 2 (
'/u02/oracle/oradata/ora9i/redo02A.log',
'/u01/oracle/oradata/ora9i/redo02B.log'
) SIZE 50M,
GROUP 3 (
'/u02/oracle/oradata/ora9i/redo03A.log',
'/u01/oracle/oradata/ora9i/redo03B.log'
) SIZE 50M,
GROUP 4 (
'/u02/oracle/oradata/ora9i/redo04A.log',
'/u01/oracle/oradata/ora9i/redo04B.log'
) SIZE 50M,
GROUP 5 (
'/u02/oracle/oradata/ora9i/redo05A.log',
'/u01/oracle/oradata/ora9i/redo05B.log'
) SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/u02/oracle/oradata/ora9i/system01.dbf',
'/u02/oracle/oradata/ora9i/indx01.dbf',
'/u02/oracle/oradata/ora9i/tools01.dbf',
'/u02/oracle/oradata/ora9i/users01.dbf',
'/u02/oracle/oradata/ora9i/users02.dbf',
'/u02/oracle/oradata/ora9i/undotbs02.dbf'
CHARACTER SET ZHS16GBK

重建以后,所有文件必须重拷贝!

--在主机上设置备机上的归档路径
startup mount
alter system set log_archive_dest_2='SERVICE=ORA9IS LGWR' scope=both;
alter system set log_archive_dest_state_2=enable scope=both;

--启动备机,启动到监听主机

!lsnrctl start(启动备机监听)

startup nomount
alter database mount standby database;
alter database recover managed standby database disconnect from session; (接收来自主机的数据变动)

--启动主机及监听

!lsnrctl start

startup

--其它事项的一些说明:

主机上做数据修改,测试Primary的归档能否应用到Standby
alter system archive log current;


停止关闭Standby
alter database recover managed standby database finish;
shutdown immediate;


切换到只读模式(备份)
主机上:shutdown immediate;
!lsnrctl stop

备机上:alter database recover managed standby database cancel;(备份开启状态,进行这类操作)
alter database open read only;


主库运行模式的说明:

select protection_mode from v$database;
MAX PERFORMANCE --最大可用模式
MAX PROTECTION --最大保护模式
MAX AVAILABILITY --最大可用模式

几者之间的转换

shutdown immediate;
startup mount;
alter database set standby database to maximize availability(ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION(最大保护));
alter database open;
需要注意:
1.在Data Guard环境中,如果要在最大保护模式下，必须至少一个Standby的设置满足以下条件
alter system set log_archive_dest_2='SERVICE=ora9is LGWR SYNC AFFIRM';
并将主库设置到Maximum Protection，备库可以在Maximum Performance，也可设置在
Maximum Protection.
2.最大保护模式下，Standby方的MRP0仍是需要等待Primary的当前日志归档后(由Log
switch触发)才能应用到database中。这在所有模式中均一样。在failover的时候，可以用
alter database recover managed standby database finish;
应用日志，这样不会丢失数据.
3.最大保护模式下，处于最大保护模式下的备用数据库不能正常的关闭，必须要保持与主
库一致的模式，如果采用abort强行关闭或者系统与网络故障，在主数据库上进行的任何事
务，将导致主数据库的关闭，在alert日志中，会有如下的记载
Tue Jun 15 12:06:04 2004
Network asynch I/O wait error 3114 log 3 service 'dbstandby'
LGWR: All standby destinations have failed (16098)
******************************************************
WARNING: All standby database destinations have failed
WARNING: Instance shutdown required to protect primary
******************************************************
LGWR: terminating instance due to error 16098
Instance terminated by LGWR, pid = 25034
4.如果备机没有启动,主机将无法启动,
因此开机顺序为:先开备机数据库,监听,再开主机监听,数据库
因此关机顺序为:先并主机数据库,监听,再关备机监听,数据库



如果要强制Primary一分种归档一次，那么设置Primary的初始化参数ARCHIVE_LAG_TARGET：
alter system set ARCHIVE_LAG_TARGET=60 scope=both;


如果想要自动在Standby上应用Primary中创建数据文件等操作，需要在Standby上设置(备份库上)扩展空间;
alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both;

可能出现的临时表空间没指定的问题(如果重建了控制文件)

1.startup --启动数据库

2.create temporary tablespace TEMP2 TEMPFILE '/home2/oracle/oradata/sysmon/temp02.dbf' SIZE 512M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED; --创建中转临时表空间

3.alter database default temporary tablespace temp2; --改变缺省临时表空间为刚刚创建的新临时表空间temp2

4.drop tablespace temp including contents and datafiles;--删除原来临时表空间

5.create temporary tablespace TEMP TEMPFILE '/home2/oracle/oradata/sysmon/temp01.dbf' SIZE 512M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED; --重新创建临时表空间

6.alter database default temporary tablespace temp; --重置缺省临时表空间为新建的temp表空间

7.drop tablespace temp2 including contents and datafiles;--删除中转用临时表空间

8.alter user roll temporary tablespace temp; --重新指定用户表空间为重建的临时表空间

如果standby机报16136错误时,处理如下:
select process,status,sequence# from v$managed_standby;
看是否存在恢复进程(MRP0 WAIT_FOR_LOG)
如果没有,就执行
RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

4.主备机切换,及主机不能运行,备机启用

--保证主备机都运行状态,同时检查主机的保护模式尽量更改为非最大保护模式

--主机完好,请首先运行一下:
alter system archive log current;

--在主机上运行:
alter database commit to switchover to physical standby;

shutdown immediate;

startup mount

alter system set standby_archive_dest='/u02/oracle/backup/arc';

--在主机上运行:

startup nomount;

alter database mount standby database;

select name,open_mode,PROTECTION_MODE,DATABASE_ROLE from v$database;

NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE
--------- ---------- -------------------- ----------------
PRIMARY MOUNTED MAXIMUM PERFORMANCE PHYSICAL STANDBY

说明主机已经变成了新的备机

alter database recover managed standby database disconnect from session;

--在备机上运行(保证日志已经写完,估计要10分钟)

alter database commit to switchover to primary;
(如果报错,startup nomount下,或者再加一步alter database mount standby database)

shutdown immediate;

startup mount

alter system set log_archive_dest_2='SERVICE=ORAPR LGWR' scope=both;
alter system set log_archive_dest_state_2=enable scope=both;

shutdown immediate

startup

select name,open_mode,PROTECTION_MODE,DATABASE_ROLE from v$database;

NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE
--------- ---------- -------------------- ----------------
ORAPR READ WRITE MAXIMUM AVAILABILITY PRIMARY

说明原来备机已经变成了主机

--主机失败的备机切换为主机

Standby Failover到Primary，此时由于故障Primary宕机或者网络不通
以下命令均在Standby端执行

1．如果是使用ARCH传递redo数据，那么执行以下命令：

检查是否有gap archive

SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

如果有则register

ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';

实行Failover：

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

ALTER DATABASE ACTIVATE STANDBY DATABASE;

ALTER DATABASE MOUNT;

ALTER DATABASE OPEN;

2．如果是使用LGWR传递redo数据，那么执行以下命令：

检查是否有gap archive

SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

如果有则register

ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';

如果是由于网络问题而导致需要切换，那么通常standby端的RFS进程并不会意识到primary已经不可访问，所以RFS进程也不会释放当前的standby redo log文件。

如果是primary端的数据库实例由于故障中断，那么一般情况下standby端的RFS进程会立刻意识到primary已经不可访问，也就会立刻释放当前的standby redo log文件。

只要RFS进程没有释放standby redo log文件，那么执行ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH命令就会在alertlog文件中发现如下的报错信息

Warning: log 4 of thread 1 is being archived or modified

Recovery interrupted.

Media Recovery failed with error 261

如果在报上述错误的时候，执行SWITCH，那么将会出现下面的错误：

ORA-16139: media recovery required

所以必须检查alertlog文件，直到发现如下信息才表示RFS进程已经释放了standby redo log文件，这时候才可以作FINISH：

RFS: Possible network disconnect with primary database

促使RFS进程释放standby redo log 文件有两种方法：

1．等待RFS进程的network timeout，通常需要等待8分钟左右

2．关闭standby数据库，再重新开启，这样会强制RFS进程释放standby redo log

我们可以通过v$managed_standby视图来监控RFS进程何时释放

实行Failover：

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

alertlog中将显示如下信息，表示finish成功：

Terminal Incomplete Recovery: UNTIL CHANGE 3738452

Terminal Incomplete Recovery: End-Of-Redo log allocation

Terminal Incomplete Recovery: log 4 reserved for thread 1 seq# 8772
........

End: All standby logfiles have been archived

Resetting standby activation ID 4038461969 (0xf0b60a11)

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH

FINSH成功之后再执行SWITCH：

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

SWITCH成功之后，重新启动数据库：

SHUTDOWN IMMEDIATE;

STARTUP;

二、实施案例2：

--利用rman实现ASM存储的standby dataguard

1\准备ASM存储的服务器
　主机：197.18.114.241(rac1)
　备机：197.18.114.242(rac2)

2\在主机上操作主机归档模式

alter database force logging;

alter system set log_archive_format='%s_%t_%r.log' scope=spfile;(设置归档文件格式)
此处与9i有不同的地方('%S_%T_%r.log',大写表示左边补0)

alter system set log_archive_dest_state_1='ENABLE';

alter system set log_archive_dest_1='location=+datagp1';(设置归档路径)

shutdown immediate

startup mount

lsnrctl stop

alter database archivelog;(启动归档,特别注意,在此时要关监听器,否则报ora 00265错误)
--alter database noarchvielog;(停止归档)
alter database open;(修改启动数据库)　

2\在主机上进行rman备份

backup as compressed backupset full database include current controlfile for standby
format '/u02/dbfull_%T_%s_%p.bak' plus archivelog format '/u02/archfull_%T_%s_%p.bak';

3\将完全备份的数据拷贝到备机上

4\从主机上操作建立备机上的参数文件

　 create pfile='/u02/standby.ora' from spfile;

将standby.ora拷贝到备机上

　并且在备机上编辑加入：
　　
*.fal_client='rac1'

　*.fal_server='rac2'

*.standby_file_management='AUTO'

5\将密码文件、init.ora文件拷贝到备机相应的位置

6\备份机上操作：

　进入asmcmd，建立与主机相应的文件夹

　create spfile='+datagp1/rac/spfilerac1.ora' from pfile='/u02/standby.ora';

7\建立tnsnames.ora(主机备机上可以相同)

rac1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 197.18.114.241)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = rac1)
)
)

rac2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 197.18.114.242)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = rac1)
)
)

8\建立主备机上的侦听，如果侦听本地可以用动态方式，侦听异机则用静态

主机上侦听器：

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 197.18.114.241)(PORT = 1521))
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = rac1)
(ORACLE_HOME = /u01/oracle/product/10.2.0)
(SID_NAME = rac1)
)
)

备机上侦听器：

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = rac1)
(ORACLE_HOME = /u01/oracle/product/10.2.0)
(SID_NAME = rac1)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 197.18.114.242)(PORT = 1521))
)
)
)

建好后可以用tnsping 测试一下

9\编辑sqlnet.ora(可以省)

NAMES.DIRECTORY_PATH= (TNSNAMES)


10\将主机+ASM\数据库实例全部关掉

　　先启监听，再打开主机数据库

11\备机上+ASM\数据库实例关闭

　先启监听，再启+ASM

　将数据库启动到nomount状态(没有控制文件，只能到这个状态)

12\将主机上的文件通过rman复制到备机，主机上进行操作

　　rman target /

connect auxiliary sys/password@rac2;(连接上备机)

---rman target sys/test@rac1 auxiliary sys/test@rac2　(两步合一步)

　 duplicate target database for standby nofilenamecheck;(对数据库进行还原)

做完duplicate后备库就自动mount上了

13\建立备机上日志文件(当前的主数据库中的在线日志文件的大小为50M，并且线程数=1 ，并且每个线程的日志文件的最大数量=3，
所以standby redo log文件组数=(3+1)*1=4执行语句如下)

　　alter database add standby logfile group　4　size 50m;

alter database add standby logfile group　5　size 50m;

查看一下日志的情况：SELECT GROUP#, THREAD#, SEQUENCE#, ARCHIVED, STATUS FROM V$STANDBY_LOG;

14\主机上建立异地归档日志

　　alter system set log_archive_dest_2='service=rac2 LGWR SYNC AFFIRM'
　　
　　--alter system set log_archive_dest_4='SERVICE=rac2 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=rac2';
对于物理备库, 虽然VALID_FOR使用默认值"VALID_FOR=(ALL_LOGFILES, ALL_ROLES)"没问题. 但是如果将来要转为逻辑备库则默认值不可以,
因为逻辑备库是open状态有自己的redolog, 所以这里设置为"VALID_FOR= (ONLINE_LOGFILES,PRIMARY_ROLE)".

alter system set log_archive_dest_state_2='ENABLE';

15\归档日志切换

　　alter system archive log current; (重复多次)

16\备机上进行恢复

　　recover managed standby database disconnect;

此时备库的alert日志文件可能会有如下错误信息:
...
ORA-00313: open failed for members of log group 4 of thread 1
Clearing online redo logfile 4 complete
...
Media Recovery Log +DG1/euisasm/archivelog/2009_06_04/thread_1_seq_164.293.688668871
Media Recovery Log +DG1/euisasm/archivelog/2009_06_04/thread_1_seq_165.292.688668871
...
不用管, 只看等待后面的归档文件是否成功完成了Media Recovery即可. 如果恢复用的全库或零级备份以来产生的归档文件很多的话, 传输归档文件和Media Recovery需要较长的时间, 通过观察alert.log可知详情.
等所有归档日志都完成了Media Recovery后, 可以尝试是否可以read only方式打开备库

17\在主机上进数据操作后，检查备机上的情况　

SQL> recover managed standby database cancel;

SQL> alter database open read only;

查询刚才增加的数据，在备机上是否存在。
　　检查完后，要重新进行redo apply

recover managed standby database disconnect;

18\备机的正常启动

　　startup nomount
alter database mount standby database;
alter database recover managed standby database disconnect from session;

　　--只读启动
　　startup nomount

alter database standby mount database;

alter database open read only;

三、日常管理

1\执行跳过，跳过的结果在
execute dbms_logstdby.skip(stmt => 'DML',schema_name => '%', object_name => '%');

跳过的内容记载在下面
select * from dba_logstdby_skip

2\停止apply

alter database stop logical standby apply;
alter database abort logical standby apply;

3\执行apply

alter database start logical standby apply;

4\实时apply

alter database start logical standby apply immediate;

跳过错误，在dba_logstdby_skip表中，ERROR列为Y
execute dbms_logstdby.skip_error('NON_SCHEMA_DDL');

5\执行apply，跳过失败的事务

alter database start logical standby apply skip failed transaction;

6\设置参数，是否记录跳过错误

exec dbms_logstdby.apply_set('RECORD_SKIP_ERRORS','FALSE');

7\设置参数，是否记录跳过DDL

exec dbms_logstdby.apply_set('RECORD_SKIP_DDL','FALSE');

8\在备库上关掉dataguard，备库可写

alter database guard none;

9\在备库上启用dataguard，备库不可写

alter database guard all;

10\官方文档

http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10823/toc.htm

11\执行某个表不通过，手工同步表

alter database stop logical standby apply;

12\创建DBLINK指向主库，然后同步创建表

exec dbms_logstdby.instantiate_table('MUGUA','SALES','dblink_name');
alter database start logical standby apply;

13\查看最后的进度
select LATEST_SCN,MINING_SCN,APPLIED_SCN,LATEST_TIME,MINING_TIME,APPLIED_TIME from V$LOGSTDBY_PROGRESS;

14\监控同步进度的脚本

SELECT * FROM dba_logstdby_log;
select * from dba_logstdby_events order by event_time desc;
select LATEST_SCN,MINING_SCN,APPLIED_SCN,LATEST_TIME,MINING_TIME,APPLIED_TIME from V$LOGSTDBY_PROGRESS;
select LOGSTDBY_ID,type,status process_status from v$logstdby_process;
select * from v$logstdby_state;
select * from v$dataguard_status order by timestamp desc;

15\增加apply的进程数
ALTER DATABASE STOP LOGICAL STANDBY APPLY; --- Stop SQL Apply
EXECUTE DBMS_LOGSTDBY.APPLY_SET('APPLY_SERVERS', 20); --- 调整apply进程数为20，默认为5个
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE; --- Start real-time Apply

16\停止apply时，如果当前正在应用，会等待执行后才停止
下面的命令可以重复执行，如果执行提示stop，则意味着正在apply还没有结束，等结束后重新执行即可
ALTER DATABASE START LOGICAL STANDBY APPLY;

四、failover的一些说明

1.如果在dataguard中做了fail over,那么原来的standby 要成为primary要执行如下命令

--首先查看v$archive_gap，然后手动将却是的archive_log拷贝过来，然后执行如下命令：
sql> alter database register physical standby 'logfile_patch'
B
sql> alter database recover managed standby database cancel;
C
sql> alter database recover managed standby database finish ;
或
sql> alter database recover managed standby database finish force;
D
sql> alter database commit to switchover to primary;

--对于老的primary 数据库需要转化为standby 有两种情况：
A.对于打开flashback 功能的数据库比较简单，现在新的primary数据库上执行这个命令
select standby_became_primary_scn from v$database;

接着在新的standby的数据库上执行
flashback database to scn nnnnnn
alter database convert to physical standby;
alter datbase recover managed standby database using current logfile disconnect from session;

在primary数据库上做日志切换，查看新的standby数据库日志是否正常。
select applied from v$archived_log;

B.如果没有开启flashback功能的话，重新创建dataguard
switchover:

在switchover的时候，最好让两个库不要再有新的session进来，这样可以快速switchover，避免发生异常

--在switchover应注意的几点：

1.在primary库要有standby logfile。
2.检查v$archive_gap,核查是否日志都已经归档到standby库

实际操作：

--在primary上查看switchover_status

SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY TO STANDBY
或
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY SESSIONS ACTIVE

状态说明：
TO STANDBY ：没有session连接，可以直接switchover
SESSIONS ACTIVE ：还有session连接，不能直接switchiver
如果SWITCHOVER_STATUS为to standby则可直接进行switchover，如果为session active则执行以下步骤：
(1)检查是否standby没接收到最新的日志，如果没有则在primary手动切换
　　SQL> alter system switch logfile;
　　系统已更改。
　　然后在standby恢复
　　SQL> alter database recover managed standby database cancel;
　　数据库已更改。
(2)检查是否有活动的sql session
　　SQL> SELECT SID, PROCESS, PROGRAM FROM V$SESSION
　　2 WHERE TYPE = 'USER'
　　3 AND SID <>(SELECT DISTINCT SID FROM V$MYSTAT);
　　未选定行

　　如果有则将session kill
--如果上述步骤都已执行而primary的switchover_status还是为session active则在正常switchover语句后加上WITH SESSION SHUTDOWN;

(1)primary进行switchover
SQL> alter database commit to switchover to physical standby WITH SESSION SHUTDOWN; 在没有活动的session下，不带WITH SESSION SHUTDOWN 选项，我也测试switchover成功
数据库已更改。

(2)重启原primary数据库到mount
SQL> select open_mode from v$database;
select open_mode from v$database
*
第 1 行出现错误:
ORA-01507: 未装载数据库

SQL> shutdown immediate;
ORA-01507: 未装载数据库

ORACLE 例程已经关闭。
SQL> startup mount;
ORACLE 例程已经启动。
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 176163716 bytes
Database Buffers 427819008 bytes
Redo Buffers 7135232 bytes
数据库装载完毕。
SQL>

查看原primary的switchover的角色
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY TO PRIMARY
SQL>
(3)检查原standby的switchover_status

SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY TO PRIMARY

(4)置于恢复模式
SQL> alter database recover managed standby database using current logfile disconnect from session;
数据库已更改。
SQL>

3。切换原standby to primary

如果上述standby的SWITCHOVER_STATUS为to primary则正常切换,否则检查是否有活动sql session,有则kill掉
　再次检查SWITCHOVER_STATUS，如果仍然不为to primary则在切换语句后加with session shutdown.
　　(1)切换standby to primary(standby需要置于接收日志的mount状态)

SQL> alter database commit to switchover to primary with session shutdown;
数据库已更改。
SQL> select open_mode from v$database;
OPEN_MODE
----------
MOUNTED
SQL> shutdown immediate;
ORA-01109: 数据库未打开

已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 234883972 bytes
Database Buffers 369098752 bytes
Redo Buffers 7135232 bytes
数据库装载完毕。
数据库已经打开。
SQL>
查看原standby库switchover后的角色

SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY
SQL>
最后还要查看两个库的alert.log内容，看是否正常

dataguard保护模式：

在更改dataguard的保护模式，一定要在primary库上执行；在primary更改的保护模式都会应用到standby库在更改保护模式时，还要注意修改 log_archive_dest_n
SQL> alter system set log_archive_dest_2='SERVICE=orcldg LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg';

实际操作：
在primary库上：
SQL> select open_mode,protection_mode,protection_level from v$database;
OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL
---------- -------------------- --------------------
READ WRITE MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
SQL> alter database set standby database to maximize protection;
alter database set standby database to maximize protection
*
第 1 行出现错误:
ORA-01126: 数据库必须已装载到此实例并且不在任何实例中打开

SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount
ORACLE 例程已经启动。
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 239078276 bytes
Database Buffers 364904448 bytes
Redo Buffers 7135232 bytes
数据库装载完毕。

SQL> alter database set standby database to maximize protection;
数据库已更改。
SQL> alter database open;
数据库已更改。
SQL>
在standby库上：
正常来说，当primary更改完保护模式后，在standby也会随着更改

SQL> select open_mode ,protection_mode,protection_level from v$database;
OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL
---------- -------------------- --------------------
MOUNTED MAXIMUM PROTECTION MAXIMUM PROTECTION

注意当保护模式更改顺序：
maximize protection ---> maximize availability ----> maximize performance
当在把dataguard的保护级别按这上面的顺序减低的时候，不需要primary库在mount状态，
primary在open状态就可以直接执行保护模式更改命令
primary的保护模式：
SQL> select open_mode ,protection_mode,protection_level from v$database;
OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL
---------- -------------------- --------------------
MOUNTED MAXIMUM PROTECTION MAXIMUM PROTECTION

执行保护模式更改：
SQL> alter database set standby database to maximize protection;
数据库已更改。
SQL> alter database set standby database to maximize availability;
数据库已更改。
在查看primary的保护模式：
SQL> select open_mode,protection_mode,protection_level from v$database;
OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL
---------- -------------------- --------------------
READ WRITE MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
SQL>

当保护模式更改顺序：
maximize protection <--- maximize availability <---- maximize performance
当在把dataguard的保护级别按这上面的顺序升高的时候，需要primary库在mount状态，如果在open更改会报如下错误：
SQL> select open_mode,protection_mode,protection_level from v$database;
OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL
---------- -------------------- --------------------
READ WRITE MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
SQL> alter database set standby database to maximize protection;
alter database set standby database to maximize protection
*
第 1 行出现错误:
ORA-01126: 数据库必须已装载到此实例并且不在任何实例中打开
步骤：
1. primary库先 shutdown immediate
2. startup mount
3. alter database set standby database to maximize protection;
4. alter database open
dataguard常用命令和视图
查看当前保护模式
SQL> select DATABASE_ROLE,PROTECTION_MODE,PROTECTION_LEVEL from v$database;
查看日志传送方式
SQL> select dest_name,archiver from v$archive_dest;
首先停止standby的自动恢复状态
SQL> alter database recover managed standby database finish;
添加standby logfile
SQL> alter database add standby logfile group 4 ('/oracle/product/10.2.0/db_1/oradata/sjh10g/redo04.log') size 50m;
更改保护模式
alter database set standby database to maximize protection;
alter database set standby database to maximize availability;
alter database set standby database to maximize performancen;
更改传输方式
SQL> alter system set log_archive_dest_2='SERVICE=orcldg LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg';

解决方法：将主备库的flashback打开：
启动到mount
SQL> select FLASHBACK_ON from v$database;
SQL> alter database flashback on;

取消自动恢复模式：
alter database recover managed standby database cancel;
alter database recover managed standby database finish;
alter database recover managed standby database finish force;
switchover 到 primary
alter dattabase commit to switchover to primary;
alter database commit to switchover to primary with session shutdown;
switchover 到 standby
alter database commit to switchover to physical standby WITH SESSION SHUTDOWN;

flashback database to scn nnnnnn
alter database convert to physical standby
alter datbase recover managed standby database using current logfile disconnect from session;
alter database register physical standby 'logfile_patch'
常用视图：
v$managed_standby
v$archive_dest
v$archive_dest_status
v$archive_gap
v$archiveg_log
v$dataguard_status
v$database
v$log_history
v$log
v$logfile

注意事项：

1、如果在主库执行 alter database clear unarchived logfile或alter database open resetlogs ,则dataguard要重建。
2、在连续恢复模式下工作之前，需要保证之前所有的归档日志己经应用到备用库上。因为在连续恢复模式的情况下，oracle不会应用之前的
归档日志，而只会应用后面陆续到来的归档日志。
3、出现归档日志gap时，需要找出相应的归档日志，然后将这些归档日志copy到备用节点的standby_archive_dest和
log_archive_dest目录下面。需要注意的是log_archive_dest目录下也需要copy。然后ALTER DATABASE RECOVER AUTOMATIC STANDBY DATABASE;
4、新建表、表空间、datafile都能通过日志应用到备库，但新建一个临时表空间，rename datafile 均不能应用到备库上.
5、应当实时察看standby库的alert文件，就能清晰明了地知道主备更新的情况。这也是排错的重要方法,切记!!
failover和switchover
Failover ：将主数据库offline，备用数据库online，这种操作由系统和软件失败引起。即使在备用数据库上应用重做日志，也可能出现数据丢失的现象，除非备用数据库运行在 guaranteed protection 模式。原主数据库重新使用时必须重新启动实例。其它的备用数据库也需重新启动实例。
Switchover ：故意将主数据库offline，而将另一备用数据库online，它能够切换到备用数据库而不需同步操作。如：可使用 Switchover完成系统的平滑升级。即使在备用数据库上不应用重做日志，也不会造成数据的丢失。数据库不需重新启动实例。这使主数据库几乎能立即在备用数据库上恢复它的功能，因此可经常进行定期维护而不需中断操作.Failover和Switchover的区别为：当Failover发生，备用数据库切换为主数据库之后，它丢失了备用数据库的所有能力，也就是说，不能再返回到备用模式；而Switchover可以，备用数据库可切换为主数据库，也可从主数据库再切换回备用数据库。

1.查看logical standby上当前正在apply的redo log
COLUMN DICT_BEGIN FORMAT A15;
COLUMN FILE_NAME FORMAT A30;
SET NUMF 9999999;
COL FCHANGE# format 9999999999999;
COL NCHANGE# for 999999999999999999999;
SET line 200
SELECT file_name, sequence# AS seq#, first_change# AS fchange#,
next_change# AS nchange#, TIMESTAMP, dict_begin AS beg,
dict_end AS END, thread# AS thr#, applied
FROM dba_logstdby_log
ORDER BY thread#;

2. 查看logical standby节点apply进程状态
select sid,type,status_code,status from v$logstdby_process;
查看applyer进程的个数
SELECT COUNT(*) AS APPLIER_COUNT FROM V$LOGSTDBY_PROCESS WHERE TYPE = 'APPLIER';
查看空闲的applyer进程
SELECT COUNT(*) AS IDLE_APPLIER
FROM V$LOGSTDBY_PROCESS WHERE TYPE = 'APPLIER' and status_code = 16166;

3. 调整logical standby的apply进程数
---Stop SQL Apply
ALTER DATABASE STOP LOGICAL STANDBY APPLY;
--- 调整apply进程数为20，默认为5个
EXECUTE DBMS_LOGSTDBY.APPLY_SET('APPLY_SERVERS', 20);
--- Start real-time Apply
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
确认logical standby上的空闲APPLIER进程
SELECT COUNT (*) AS idle_applier
FROM v$logstdby_process
WHERE TYPE = 'APPLIER' AND status_code = 16166;
注：status_code = 16166 表示进程是空闲状态，可以看到"STATS"为"ORA-16116: no work available"

主机上查询：

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
4 0 0 YES UNASSIGNED
5 0 0 YES UNASSIGNED
6 0 0 YES UNASSIGNED

SQL> alter system switch logfile;

System altered.

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
4 0 0 YES UNASSIGNED
5 0 0 YES UNASSIGNED
6 0 0 YES UNASSIGNED

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
4 0 0 YES UNASSIGNED
5 0 0 YES UNASSIGNED
6 0 0 YES UNASSIGNED

SQL> SELECT * FROM V$LOGFILE;

GROUP# STATUS TYPE MEMBER
---------- ------- ------- -----------------------------------------------------------------------------------------
3 ONLINE E:\ORACLE\PRODUCT\10.2.0\ORADATA\PRODDB\REDO03.LOG
2 ONLINE E:\ORACLE\PRODUCT\10.2.0\ORADATA\PRODDB\REDO02.LOG
1 ONLINE E:\ORACLE\PRODUCT\10.2.0\ORADATA\PRODDB\REDO01.LOG
4 STANDBY E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\PRODDB\ONLINELOG\O1_MF_4_5GOT1HY5_.LOG
5 STANDBY E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\PRODDB\ONLINELOG\O1_MF_5_5GOT1KX6_.LOG
6 STANDBY E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\PRODDB\ONLINELOG\O1_MF_6_5GOO21ML_.LOG

6 rows selected.

SQL> select
2 substr(local.name,1,50) "Archive Name",
3 case when remote.sequence# is null then 'NOT TRANSMITTED'
4 else 'transmitted'
5 end,
6 local.sequence#,
7 local.thread#
8 from
9 (select * from v$archived_log where dest_id = 1) local
10 left join
11 (select * from v$archived_log where dest_id = 2) remote
12 on local.sequence# = remote.sequence# and
13 local.thread# = remote.thread#
14 order by local.sequence#;

Archive Name CASEWHENREMOTE. SEQUENCE# THREAD#
-------------------------------------------------- --------------- ---------- ----------
E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\PRODD NOT TRANSMITTED 3 1
E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\PRODD transmitted 4 1
E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\PRODD transmitted 5 1
E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\PRODD transmitted 6 1
E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\PRODD transmitted 7 1
E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\PRODD transmitted 8 1
E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\PRODD transmitted 9 1

7 rows selected.

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
6 1 9 YES ACTIVE

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER
---------- ------- ------- -----------------------------------------------------------------------------------------------------
3 ONLINE E:\ORACLE\PRODUCT\10.2.0\ORADATA\STANDDB\REDO03.LOG
2 ONLINE E:\ORACLE\PRODUCT\10.2.0\ORADATA\STANDDB\REDO02.LOG
1 ONLINE E:\ORACLE\PRODUCT\10.2.0\ORADATA\STANDDB\REDO01.LOG
6 STANDBY E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\PRODDB\ONLINELOG\O1_MF_6_5GOO21ML_.LOG

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
6 1 0 NO UNASSIGNED

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
6 1 0 NO UNASSIGNED

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$LOGFILE;
SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$LOGFILE
*
ERROR at line 1:
ORA-00904: "ARCHIVED": invalid identifier

查看间隙：
SQL> select * from v$archive_gap;

SQL> SELECT * FROM V$LOGFILE;

GROUP# STATUS TYPE MEMBER
---------- ------- ------- -----------------------------------------------------------------------------------------------------
3 ONLINE E:\ORACLE\PRODUCT\10.2.0\ORADATA\STANDDB\REDO03.LOG
2 ONLINE E:\ORACLE\PRODUCT\10.2.0\ORADATA\STANDDB\REDO02.LOG
1 ONLINE E:\ORACLE\PRODUCT\10.2.0\ORADATA\STANDDB\REDO01.LOG
6 STANDBY E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\PRODDB\ONLINELOG\O1_MF_6_5GOO21ML_.L

如何根据系统表现调整APPLIER进程的个数，统计transactions的apply状态，
SQL> SELECT NAME, VALUE FROM V$LOGSTDBY_STATS WHERE NAME LIKE 'transactions%';
NAME VALUE
-------------------------- ----------------------------
transactions ready 159
transactions applied 159

如果ready(等待apply)和applied(已经apply)的值基本同步，则设置的APPLIER进程合适或偏多。根据IDLE_APPLIER的进程数，可减少APPLIER进程数目.如果transactions ready - transactions applied的差比APPLIER进程数的2倍还多，则需要增加APPLIER进程数目了。

4. 调整PREPARER(调制机)的进程数
logical standby上有很多transactions等待apply, 但是还有空闲的applyer进程，且已经没有idle状态的PREPARER进程，这时需要增加PREPARER(调制机)的进程数。如下：

ALTER DATABASE STOP LOGICAL STANDBY APPLY; --- Stop SQL Apply
EXECUTE DBMS_LOGSTDBY.APPLY_SET('PREPARE_SERVERS', 4); --- 调整PREPARER进程数为4
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE; --- Start real-time Apply

5. 计算logical standby的apply速度
可以通过v$logstdby_stats视图统计logical standby的apply速度，计算公式如下：
apply_rate = bytes of redo processed / (coordinator uptime - seconds system is idle)

比如：
SELECT NAME,VALUE FROM v$logstdby_stats
WHERE NAME IN ('coordinator uptime','seconds system is idle','bytes of redo processed');
NAME VALUE
--------------------------- ---------------------------------
coordinator uptime 78717
bytes of redo processed 7954813012
seconds system is idle 40
logical standby.apply_rate = 7954813012/(78717-40)/1024/1024
整理成1条sql语句计算出apply_rate如下：

SELECT c.VALUE / (a.VALUE - b.VALUE) / 1024 / 1024 AS "APPLY_RATE"
FROM v$logstdby_stats a, v$logstdby_stats b, v$logstdby_stats c
WHERE a.NAME = 'coordinator uptime'
AND b.NAME = 'seconds system is idle'
AND c.NAME = 'bytes of redo processed';
6. 统计logical standby上是否有报错信息
SELECT xidusn, xidslt, xidsqn, status, status_code
FROM dba_logstdby_events
WHERE event_time = (SELECT MAX (event_time)
FROM dba_logstdby_events);

7. 调整MAX_SGA - 防止Pageouts

SQL> select value bytes from v$logstdby_stats where name='bytes paged out';
注：如果以上查询结果在增长,则查到当前MAX_SGA的大小:
SQL> select value from v$logstdby_stats where name = 'maximum SGA for LCR cache';
VALUE
------------------------
30
增大MAX_SGA，备注:10gR2,MAX_SGA可以增大到4095 MB.
SQL> alter database stop logical standby apply;
Database altered.
SQL> execute dbms_logstdby.apply_set('MAX_SGA',1000);
PL/SQL procedure successfully completed.
SQL> alter database start logical standby apply immediate;
Database altered.
逻辑备库需要将redo记录解析成LCR，会在shared pool里分配一部分空间来作为LCR Cache，如果cache太小，就会像OS的虚拟内存管理一样，需要做page out，这会严重影响应用日志的性能。默认情况下，LCR Cache为Shared pool的四分之一，最少不少于30M，否则SQL Apply不能启动。如果机器的内存足够，建议将LCR Cache尽量设大一点，当然，同时share pool也要足够大。如果机器内存有限，那可以考虑将buffer cache减少一点来给LCR Cache腾出空间。

8.跳过特定表或Schema的DML或DDL事务
alter database stop logical standby apply;
execute dbms_logstdby.skip (stmt =>'DML',schema_name=>'TEST',
object_name =>'SALES', proc_name =>null);
execute dbms_logstdby.skip (stmt =>'SCHEMA_DDL',schema_name=>'TEST',
object_name =>'SALES', proc_name => null);
execute dbms_logstdby.skip (stmt =>'DML',
schema_name =>'TEST', object_name =>'%', proc_name=>null);
execute dbms_logstdby.skip (stmt =>'SCHEMA_DDL',
schema_name =>'TEST', object_name =>'%', proc_name=>null);
alter database start logical standby apply;

9.如何重新初始化数据表
通过以下查询确认当前的skip规则:
select * from dba_logstdby_skip;
建议取消Skip之后，再重新初始化:
alter database stop logical standby apply;
execute dbms_logstdby.unskip('DML','TEST','SALES');
exec dbms_logstdby.instantiate_table('TEST','SALES','dblink_name');
alter database start logical standby apply;
10. Starting Real-time Apply
To start real-time apply on the logical standby database to immediately recover redo data from the standby redo log files on the logical standby database, include the IMMEDIATE keyword as shown in the following statement:
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;11.Stopping Log Apply Services on a Logical Standby Database
To stop SQL Apply, issue the following statement on the logical standby database:
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;When you issue this statement, SQL Apply waits until it has committed all complete transactions that were in the process of being applied. Thus, this command may not stop the SQL Apply processes immediately.
If you want to stop SQL Apply immediately, issue the following statement:
SQL> ALTER DATABASE ABORT LOGICAL STANDBY APPLY;
Oracle文档参考：
INSTANTIATE_TABLE Procedure
This procedure creates and populates a table in the standby database from a corresponding table in the primary database. The table requires the name of the database link (dblink) as an input parameter.
Use the INSTANTIATE_TABLE procedure to:
Add a table to a standby database
Re-create a table in a standby database
Syntax
DBMS_LOGSTDBY.INSTANTIATE_TABLE ( table_name IN VARCHAR2, schema_name IN VARCHAR2, dblink IN VARCHAR2);Parameters
Table 29-4 describes the parameters for the INSTANTIATE_TABLE procedure.
Table 29-4 DBMS_LOGSTDBY.INSTANTIATE_TABLE Procedure Parameters
Parameter Description
table_name
Name of the table to be created or re-created in the standby database.

schema_name
Name of the schema.

dblink
Name of the database link account that has privileges to read and lock the table in the primary database.
Exceptions
None.
Usage Notes
Use this procedure to create and populate a table in a way that keeps the data on the standby database transactionally consistent with the primary database.
This procedure assumes that the metadata has been maintained correctly.
This table is not safe until the redo log that was current on the primary database at the time of execution is applied to the standby database.
Example
Enter this statement to create and populate a new table on the standby database.
SQL> EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE ('myschema', 'mytable', 'mydblink');

　　正确的开关机顺序是：

　　启动的时候，先备库的listener,再启动备库,再启动主库的listener,再启动主库。

　　关闭的时候，先关闭主库，再关闭备库。

　　--为主数据库或备用数据库添加/删除日志组　　

SQL> alter database add standby logfile group 5 '/oracle/oradata/orcl/standbyredo05.log' size 100M;
　　SQL> alter database drop standby logfile group 5;

　　--查询DataGuard当前处于哪种日志传输方式：　

　SQL> select process,client_process,sequence#,status from v$managed_standby;

　　PROCESS列显示进程信息

　　CLIENT_PROCESS列显示对应的主数据库中的进程

　　SEQUENCE#列显示归档redo的序列号

　　STATUS列显示的进程状态

　　--查询standby库中所有已被应用的归档文件信息(不论该归档文件是否还存在)　

　SQL> select first_time,first_change#,next_change#,sequence# from v$log_history;

　　--最后一个被应用的log　　

SQL> SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG" FROM V$LOG_HISTORY GROUP BY THREAD#;

　　--在primary server上查询有哪些日志没有被传输到Standby　

SQL> SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) LOCAL WHERE LOCAL.SEQUENCE# NOT IN
　　(SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND THREAD# = LOCAL.THREAD#);

　　--对于troubleshooting有用

　SQL> SELECT MESSAGE FROM V$DATAGUARD_STATUS;

　　m.监控恢复操作的进程　
SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS;

　　--从正在恢复状态只读打开;　

SQL> Alter DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
　　SQL> Alter DATABASE OPEN READ ONLY;

　　--切换回到恢复状态;　　

SQL> Alter DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

　　切换角色

　　注意：Swithover时只能先从Primary切到Standby，再从Standby切到Primary.

　　1、在主库端

select database_role,switchover_status from v$database;

　　如果是to standby 表可以正常切换.

　　直接执行 ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

　　否则执行: ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;　

SQL> shutdown immediate;
　　SQL> startup nomount;
　　SQL> alter database mount standby database;
　　SQL> select database_role from v$database;
　　SQL> alter database recover managed standby database disconnect from session;

　　2、在备库端　　

select database_role,switchover_status from v$database;

　　如果是to_primary 表可以正常切换.

　　执行:ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

　　否则执行: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;　　

shutdown immediate;
　　startup;

　　然后观察主备库日志,如果正常的话会看到备库会自动应用日志.

　 failover测试

　　1. 备库上检查是否存在归档中断　

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

　　2. 在主库上执行语句并找出归档文件

SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND DEST_ID=1 AND SEQUENCE# BETWEEN XX AND XX;

　　--如果存在拷贝相应的归档到STANDBY数据库，并注册.　　

SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'xxx';

　　***其他情况(primary数据库无法打开)：

　　--检查归档文件是否完整

　　分别在primary/standby执行下列语句：　　

SQL> select distinct thread#,max(sequence#) over(partition by thread#) a from v$archived_log;

　　把相差的归档复制到待转换的standby服务器

　　3. 启动failover　　

SQL> alter database recover managed standby database finish force;

　　FORCE关键字将会停止当前活动的RFS进程，以便立刻执行failover。

　　或　

SQL> alter database recover managed standby database finish skip standby logfile;

　　4. 切换物理standby角色为primary　

　SQL> alter database commit to switchover to primary;
　　SQL> shutdown immediate
　　SQL> startup

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/爱喝兽奶帝天荒/article/detail/976422