1.fail over
2.switchover
3.更改保护模式
4.常用命令和视图
fail over：
1.如果在dataguard中做了fail over,那么原来的standby 要成为primary要执行如下命令
A
首先查看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库
实际操作：
1.在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
2。如果上述步骤都已执行而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"
如何根据系统表现调整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');
