实验说明：
熟悉Ogg实施过程的伙伴们都应了解，实施步骤中初始化的步骤尤为关键，通常可以使用rman或者exp两种方式，而开启复制进程时也有atcsn（大于或等于该scn的事务将被apply），aftercsn（大于该scn的事务将被apply）参数进行选择。
1
2
3
4 | 结论：当使用rman初始化时使用atcsn，使用exp初始化时使用aftercsn。
真假：真
说法：以上结论正确，但存在特例：当rman方式初始化时，如果until 的scn正好是一个事务的commit消息，则需要使用aftercsn；因为此时rman恢复包含了该事务。
真假：假
:—
下面的实验同时验证说法的不正确以及结论的正确性。
实验环境：
数据库版本：oracle 11.2.0.4
操作系统：redhat 5.5
实验步骤：
一、创建表xt
1
2
3
4 | Create table xt (i int);
插入数据并提交
insert into xt select current_scn from v$database;
commit;
:—
二、使用logminer查看该事务的scn
1)找出正在使用的日志文件
1
2
3
4
5
6
7
8 | SQL> select l.group#, l.thread#, l.status, lf.member
from v$log l, v$logfile lf
where l.status = 'CURRENT'
and l.group# = lf.group#;
GROUP# THREAD# STATUS MEMBER
———- ———- —————- —————————–
1 1 CURRENT +DATADG/sprac/onlinelog/group_1.257.897514033
:—
2)将日志文件加入挖掘队列
1
2
3
4 | SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE('+DATADG/sprac/onlinelog/group_1.257.897514033',
DBMS_LOGMNR.NEW);
PL/SQL procedure successfully completed.
:—
3)使用联机目录数据字典
1
2
3 | SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
PL/SQL procedure successfully completed.
:—
4)查看事务的commi scn，commit scn 为17597245
1
2
3
4
5
6
7
8
9 | SQL> select scn, start_scn, commit_scn, operation, table_name, sql_redo
from v$logmnr_contents
where scn between 17597237 and 17597245;
SCN START_SCN COMMIT_SCN OPERATION TABLE_NAME SQL_REDO
———- ————- —————– ———- ————- ————————–
17597238 START set transaction read write;
17597238 INSERT XT insert into "SYS"."XT"("I") values ('17597237');
17597245 17597238 17597245 COMMIT commit;
:—
使用闪回查询，在17597245点可以看到该事务，在17597244点是没有该事务的
1
2
3
4
5
6
7
8 | SQL> select * from xt as of scn 17597245;
I
17597237
SQL> select * from xt as of scn 17597244;
no rows selected
:—
三、使用exp闪回导出数据，导出一行说明可以导出该事务
1
2
3
4
5
6
7
8
9
10
11 | [oracle@sprac1 ~]$ exp system/oracle file=/tmp/xt tables=sys.xt flashback_scn=17597245
Export: Release 11.2.0.4.0 – Production on Mon Dec 7 11:30:08 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Tes
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path …
Current user changed to SYS
. . exporting table XT 1 rows exported
Export terminated successfully without warnings.
:—
四、使用rman恢复
1)关闭数据库
1
2
3
4 | SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
:—
2)使用rman打开到mount
1
2
3
4
5
6
7
8
9
10
11
12 | [oracle@sprac1 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 – Production on Mon Dec 7 11:35:35 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup mount;
Oracle instance started
database mounted
Total System Global Area 818401280 bytes
Fixed Size 2257680 bytes
Variable Size 645926128 bytes
Database Buffers 167772160 bytes
Redo Buffers 2445312 bytes
:—
3)使用已经做好的全备Restore数据库
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20 | RMAN> restore database;
Starting restore at 07-DEC-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 instance=sprac1 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATADG/sprac/datafile/system.259.885043239
channel ORA_DISK_1: restoring datafile 00002 to +DATADG/sprac/datafile/sysaux.260.885043267
channel ORA_DISK_1: restoring datafile 00003 to +DATADG/sprac/datafile/undotbs1.261.885043287
channel ORA_DISK_1: restoring datafile 00004 to +DATADG/sprac/datafile/undotbs2.266.887480495
channel ORA_DISK_1: restoring datafile 00005 to +DATADG/sprac/datafile/users.264.885043319
channel ORA_DISK_1: restoring datafile 00006 to +DATADG/sprac/datafile/xjruser.dbf
channel ORA_DISK_1: restoring datafile 00007 to +DATADG/sprac/datafile/ogg.473.886860363
channel ORA_DISK_1: restoring datafile 00008 to +DATADG/sprac/datafile/sh.606.892830263
channel ORA_DISK_1: reading from backup piece /disk/full_sprac1.full
channel ORA_DISK_1: piece handle=/disk/full_sprac1.full tag=TAG20151201T195635
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:57
Finished restore at 07-DEC-15
:—
4)恢复数据库until scn 17597245
1
2
3
4
5
6
7
8
9
10
11
12 | RMAN> recover database until scn 17597245;
Starting recover at 07-DEC-15
using channel ORA_DISK_1
starting media recovery
…
…
…
archived log file name=/u01/app/oracle/arch/sprac/1_20_897514028.dbf thread=1 sequence=20
archived log file name=/u01/app/oracle/arch/sprac/1_21_897514028.dbf thread=1 sequence=21
archived log file name=/u01/app/oracle/arch/sprac/1_1_897773893.dbf thread=1 sequence=1
media recovery complete, elapsed time: 00:02:16
Finished recover at 07-DEC-15
:—
5)进入sqlplus，以read only模式打开
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21 | [oracle@sprac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 7 11:43:27 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16005: database requires recovery————此处由于控制文件scn新于数据文件，执行下面语句即可
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 17597245 generated at 12/07/2015 11:18:21 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/sprac/1_3_897773893.dbf
ORA-00280: change 17597245 for thread 1 is in sequence #3
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open read only;
Database altered.
:—
6)查看数据文件头scn
1
2
3
4
5
6
7
8
9
10
11
12 | SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
17597245
17597245
17597245
17597245
17597245
17597245
17597245
17597245
8 rows selected.
:—
7)查看current scn，说明数据库只包含到17597244
1
2
3
4 | SQL> select current_scn from v$database;
CURRENT_SCN
17597244
:—
8)查看xt表的事务存不存在
1
2 | SQL> select * from xt;
no rows selected
:—
试验总结：
rman recover until scn 17597245是不包含这个scn，所以ogg复制进程需要使用atcsn 17597245；
Exp flashback_scn 17597245是包含这个scn的，所以复制进程使用aftercsn 17597245
Tips：同理 splex的reconcile操作，
Rman 方式初始化————》reconcile scn xxx
Exp 方式初始化—————》reconcile scn xxx+1
此条目发表在Oracle, ORACLE基础与管理分类目录。将固定链接加入收藏夹。