首页 > 代码库 > OracleDG主库丢失归档增量同步
OracleDG主库丢失归档增量同步
1.1 问题
在主库archivelog丢失后，数据无法同步到备库时，可以利用增量scn的方式，来避免全库还原备库或重建standby
以下在Oracle10g下操作
1.2 当前正常主备
确认当前主库与备库是同步的：
1.2.1 主库
SQL> SELECT database_role FROM v$database;
DATABASE_ROLE
----------------
PRIMARY
SQL> set linesize 200 pagesize 200
SQL> select group#,sequence#,status,first_time,to_char(first_change#) as begin_scn from v$log order by group# asc;
GROUP# SEQUENCE# STATUS FIRST_TIME BEGIN_SCN
---------- ---------- ---------------- -------------- ----------------
1 192 CURRENT 03-9月 -13 1883016
2 190 INACTIVE 03-9月 -13 1882999
3 191 INACTIVE 03-9月 -13 1883014
SQL> select count(*) from t;
COUNT(*)
----------
4
SQL> archive log list;
数据库日志模式 存档模式
自动存档 启用
存档终点 /archive
最早的联机日志序列 190
下一个存档日志序列 192
当前日志序列 192
1.2.2 备库
SQL> select process,status,thread#,sequence#,block#,blocks from V$managed_standby;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CLOSING 1 189 1 1
ARCH CLOSING 1 191 1 1
ARCH CLOSING 1 188 40961 1824
ARCH CLOSING 1 190 1 4
ARCH CONNECTED 0 0 0 0
MRP0 WAIT_FOR_LOG 1 192 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
已选择13行。
1.3 模拟主库丢失归档
1.3.1 禁用归档至备库
SQL> SELECT * FROM v$Log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ------- ---------- --- ---------------- -------------
1 1 192 52428800 3 YES INACTIVE 1883016 03-9月 -13
2 1 193 52428800 3 YES INACTIVE 1883668 03-9月 -13
3 1 194 52428800 3 NO CURRENT 1883688 03-9月 -13
SQL> ALTER system SET log_archive_dest_state_2 = ‘defer‘;
系统已更改
1.3.2 测试数据准备
SQL> delete from t where id<10;
已删除3行。
SQL> commit;
提交完成。
SQL> select * from t;
ID
----------
111
SQL> ALTER system switch logfile;
系统已更改。
SQL> ALTER system switch logfile;
系统已更改。
SQL> ALTER system switch logfile;
系统已更改。
1.3.3 删除未传输到备库的某个归档
oracle@linux-7gnw:~> cd /archive/
oracle@linux-7gnw:/archive> ls -lrt
total 1935908
-rw-r----- 1 oracle oinstall 1024 2013-09-03 11:30 sdk_1_819054888_189.dbf
-rw-r----- 1 oracle oinstall 21905920 2013-09-03 11:31 sdk_1_819054888_188.dbf
-rw-r----- 1 oracle oinstall 2560 2013-09-03 11:31 sdk_1_819054888_190.dbf
-rw-r----- 1 oracle oinstall 1024 2013-09-03 11:31 sdk_1_819054888_191.dbf
-rw-r----- 1 oracle oinstall 204288 2013-09-03 11:55 sdk_1_819054888_192.dbf
-rw-r----- 1 oracle oinstall 4608 2013-09-03 11:56 sdk_1_819054888_193.dbf
-rw-r----- 1 oracle oinstall 3465728 2013-09-03 14:09 sdk_1_819054888_194.dbf
-rw-r----- 1 oracle oinstall 1024 2013-09-03 14:09 sdk_1_819054888_195.dbf
-rw-r----- 1 oracle oinstall 2560 2013-09-03 14:09 sdk_1_819054888_196.dbf
oracle@linux-7gnw:/archive> rm sdk_1_819054888_195.dbf
oracle@linux-7gnw:/archive> rm sdk_1_819054888_196.dbf
SQL> ALTER system switch logfile;
系统已更改。
SQL> ALTER system SET log_archive_dest_state_2 = ‘enable‘;
系统已更改。
1.3.4 主库报错
Tue Sep 3 14:13:08 2013
Errors in file /oracle/admin/sdk/bdump/sdk_arc4_4339.trc:
ORA-00308: cannot open archived log ‘/archive/sdk_1_819054888_196.dbf‘
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Tue Sep 3 14:13:08 2013
FAL[server, ARC4]: FAL archive failed, see trace file.
Tue Sep 3 14:13:08 2013
Errors in file /oracle/admin/sdk/bdump/sdk_arc4_4339.trc:
ORA-16055: FAL request rejected
ARCH: FAL archive failed. Archiver continuing
Tue Sep 3 14:13:08 2013
ORACLE Instance sdk - Archival Error. Archiver continuing.
Tue Sep 3 14:13:08 2013
Errors in file /oracle/admin/sdk/bdump/sdk_arc3_4337.trc:
ORA-00308: cannot open archived log ‘/archive/sdk_1_819054888_195.dbf‘
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Tue Sep 3 14:13:08 2013
FAL[server, ARC3]: FAL archive failed, see trace file.
Tue Sep 3 14:13:08 2013
Errors in file /oracle/admin/sdk/bdump/sdk_arc3_4337.trc:
ORA-16055: FAL request rejected
ARCH: FAL archive failed. Archiver continuing
Tue Sep 3 14:13:08 2013
ORACLE Instance sdk - Archival Error. Archiver continuing.
1.3.5 备库也报错
SQL> ALTER DATABASE recover managed standby DATABASE cancel;
数据库已更改。
SQL> alter database recover managed standby database parallel 4 using current logfile disconnect from session;
数据库已更改。
SQL> select process,status,thread#,sequence#,block#,blocks from V$managed_standby;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
MRP0 WAIT_FOR_GAP 1 195 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
已选择13行。
主库丢失了两个归档日志，备库无法同步
alter database recover managed standby database parallel 4 using current logfile disconnect from session
Tue Sep 3 14:12:18 2013
Attempt to start background Managed Standby Recovery process (sdk)
MRP0 started with pid=24, OS id=5134
Tue Sep 3 14:12:18 2013
MRP0: Background Managed Standby Recovery process started (sdk)
Managed Standby Recovery starting Real Time Apply
parallel recovery started with 4 processes
Tue Sep 3 14:12:24 2013
Waiting for all non-current ORLs to be archived...
Tue Sep 3 14:12:24 2013
Completed: alter database recover managed standby database parallel 4 using current logfile disconnect from session
Tue Sep 3 14:12:25 2013
Media Recovery Waiting for thread 1 sequence 195
Fetching gap sequence in thread 1, gap sequence 195-196
Tue Sep 3 14:13:26 2013
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 195-196
DBID 3126144103 branch 819054888
FAL[client]: All defined FAL servers have been attempted.
-------------------------------------------------------------
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that is sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.
-------------------------------------------------------------
1.4 恢复同步
1.4.1 定位到SCN
根据GAP情况 GAP - thread 1 sequence 195-196
SQL> SELECT SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE# FROM v$archived_log
WHERE SEQUENCE# > 194 ORDER BY 1;
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ------------- ------------
195 1888252 1888254
196 1888254 1888257
197 1888257 1888287
197 1888257 1888287
1.4.2 从丢失归档时SCN备份
195号规定对应的SCN是1888252
RMAN> backup device type disk incremental from scn 1888252 database format ‘/oracle/lost_archive_%u_%s_%p.bk‘;
启动 backup 于 03-9月 -13
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: sid=131 devtype=DISK
通道 ORA_DISK_1: 启动全部数据文件备份集
通道 ORA_DISK_1: 正在指定备份集中的数据文件
输入数据文件 fno=00002 name=/oracle/oradata/sdk/undotbs01.dbf
输入数据文件 fno=00001 name=/oracle/oradata/sdk/system01.dbf
输入数据文件 fno=00003 name=/oracle/oradata/sdk/sysaux01.dbf
输入数据文件 fno=00004 name=/oracle/oradata/sdk/users01.dbf
通道 ORA_DISK_1: 正在启动段 1 于 03-9月 -13
通道 ORA_DISK_1: 已完成段 1 于 03-9月 -13
段句柄=/oracle/lost_archive_06oiu8cg_6_1.bk 标记=TAG20130903T143248 注释=NONE
通道 ORA_DISK_1: 备份集已完成, 经过时间:00:00:45
通道 ORA_DISK_1: 启动全部数据文件备份集
通道 ORA_DISK_1: 正在指定备份集中的数据文件
备份集中包括当前控制文件
在备份集中包含当前的 SPFILE
通道 ORA_DISK_1: 正在启动段 1 于 03-9月 -13
通道 ORA_DISK_1: 已完成段 1 于 03-9月 -13
段句柄=/oracle/lost_archive_07oiu8du_7_1.bk 标记=TAG20130903T143248 注释=NONE
通道 ORA_DISK_1: 备份集已完成, 经过时间:00:00:03
完成 backup 于 03-9月 -13
1.4.3 查看备份片
oracle@linux-7gnw:~> ls -lrt /oracle/
total 1165084
drwxr-xr-x 3 oracle oinstall 4096 2013-06-25 18:46 product
drwxr-xr-x 3 oracle oinstall 4096 2013-06-25 19:14 oradata
-rw-r----- 1 oracle oinstall 630784 2013-09-03 14:33 lost_archive_06oiu8cg_6_1.bk
-rw-r----- 1 oracle oinstall 7438336 2013-09-03 14:33 lost_archive_07oiu8du_7_1.bk
1.4.4 传输备份片到备库
oracle@linux-7gnw:~> cd /oracle/
oracle@linux-7gnw:/oracle> scp lost_archive_06oiu8cg_6_1.bk lost_archive_07oiu8du_7_1.bk oracle@192.168.142.163:/archive
Password:
lost_archive_06oiu8cg_6_1.bk 100% 616KB 616.0KB/s 00:00
lost_archive_07oiu8du_7_1.bk 100% 7264KB 3.6MB/s 00:02
然后去备库还原这2个文件：
1.4.5 备库增量还原
SQL> select process,status,thread#,sequence#,block#,blocks from V$managed_standby;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
MRP0 WAIT_FOR_GAP 1 195 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
已选择13行。
SQL> ALTER DATABASE recover managed standby DATABASE cancel;
数据库已更改。
oracle@linux-5pzu:~> rman target /
恢复管理器: Release 10.2.0.4.0 - Production on 星期二 9月 3 14:36:52 2013
Copyright (c) 1982, 2007, Oracle. All rights reserved.
已连接到目标数据库: SDK (DBID=3126144103, 未打开)
RMAN> catalog backuppiece ‘/archive/lost_archive_06oiu8cg_6_1.bk‘;
使用目标数据库控制文件替代恢复目录
已列入目录的备份段
备份段 handle=/archive/lost_archive_06oiu8cg_6_1.bk recid=1 stamp=825172729
RMAN> catalog backuppiece ‘/archive/lost_archive_07oiu8du_7_1.bk‘;
已列入目录的备份段
备份段 handle=/archive/lost_archive_07oiu8du_7_1.bk recid=2 stamp=825172787
RMAN> recover DATABASE noredo;
启动 recover 于 03-9月 -13
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: sid=147 devtype=DISK
通道 ORA_DISK_1: 正在开始恢复增量数据文件备份集
通道 ORA_DISK_1: 正在指定从备份集还原的数据文件
数据文件 00001 的还原目标: /oracle/oradata/sdk/system01.dbf
数据文件 00002 的还原目标: /oracle/oradata/sdk/undotbs01.dbf
数据文件 00003 的还原目标: /oracle/oradata/sdk/sysaux01.dbf
数据文件 00004 的还原目标: /oracle/oradata/sdk/users01.dbf
通道 ORA_DISK_1: 正在读取备份片段 /archive/lost_archive_06oiu8cg_6_1.bk
通道 ORA_DISK_1: 已还原备份片段 1
段句柄 = /archive/lost_archive_06oiu8cg_6_1.bk 标记 = TAG20130903T143248
通道 ORA_DISK_1: 还原完成, 用时: 00:00:03
完成 recover 于 03-9月 -13
RMAN> exit
恢复管理器完成。
1.4.6 启动同步
oracle@linux-5pzu:~> sqlplus /nolog
SQL*Plus: Release 10.2.0.4.0 - Production on 星期二 9月 3 14:41:27 2013
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
SQL> connect / as sysdba
已连接。
SQL> alter database recover managed standby database parallel 4 using current logfile disconnect from session;
数据库已更改。
SQL> SELECT database_role,open_mode FROM v$database;
DATABASE_ROLE OPEN_MODE
---------------- ----------
PHYSICAL STANDBY MOUNTED
Tue Sep 3 14:35:38 2013
ALTER DATABASE recover managed standby DATABASE cancel
Tue Sep 3 14:35:56 2013
MRP0: Background Media Recovery cancelled with status 16037
Tue Sep 3 14:35:56 2013
Errors in file /oracle/admin/sdk/bdump/sdk_mrp0_5134.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Tue Sep 3 14:35:56 2013
Errors in file /oracle/admin/sdk/bdump/sdk_mrp0_5134.trc:
ORA-16037: user requested cancel of managed recovery operation
Tue Sep 3 14:35:56 2013
MRP0: Background Media Recovery process shutdown (sdk)
Tue Sep 3 14:35:56 2013
Managed Standby Recovery Canceled (sdk)
Tue Sep 3 14:35:56 2013
Completed: ALTER DATABASE recover managed standby DATABASE cancel
Tue Sep 3 14:40:42 2013
Incremental restore complete of datafile 4 /oracle/oradata/sdk/users01.dbf
checkpoint is 1888992
Incremental restore complete of datafile 3 /oracle/oradata/sdk/sysaux01.dbf
checkpoint is 1888992
Incremental restore complete of datafile 1 /oracle/oradata/sdk/system01.dbf
checkpoint is 1888992
Incremental restore complete of datafile 2 /oracle/oradata/sdk/undotbs01.dbf
checkpoint is 1888992
Tue Sep 3 14:41:36 2013
alter database recover managed standby database parallel 4 using current logfile disconnect from session
Tue Sep 3 14:41:36 2013
Attempt to start background Managed Standby Recovery process (sdk)
MRP0 started with pid=24, OS id=5277
Tue Sep 3 14:41:37 2013
MRP0: Background Managed Standby Recovery process started (sdk)
Managed Standby Recovery starting Real Time Apply
parallel recovery started with 4 processes
Tue Sep 3 14:41:46 2013
Waiting for all non-current ORLs to be archived...
Media Recovery Waiting for thread 1 sequence 195
Fetching gap sequence in thread 1, gap sequence 195-196
Tue Sep 3 14:41:46 2013
Completed: alter database recover managed standby database parallel 4 using current logfile disconnect from session
Tue Sep 3 14:42:47 2013
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 195-196
DBID 3126144103 branch 819054888
FAL[client]: All defined FAL servers have been attempted.
-------------------------------------------------------------
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that is sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.
-------------------------------------------------------------
1.4.7 主库归档位置
SELECT MAX(al.SEQUENCE#) "Last Seq Recieved",MAX(lh.SEQUENCE#) "Last Seq Applied"
FROM v$archived_log al, v$log_history lh;
SQL> SELECT database_role,open_mode FROM v$database;
DATABASE_ROLE OPEN_MODE
---------------- ----------
PRIMARY READ WRITE
SQL> ALTER system switch logfile;
系统已更改。
SELECT MAX(al.SEQUENCE#) "Last Seq Recieved",MAX(lh.SEQUENCE#) "Last Seq Applied"
2 FROM v$archived_log al, v$log_history lh;
Last Seq Recieved Last Seq Applied
----------------- ----------------
198 198
1.4.8 备库归档位置
SQL> select process,status,thread#,sequence#,block#,blocks from V$managed_standby;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CLOSING 1 198 1 777
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
MRP0 WAIT_FOR_GAP 1 195 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
已选择14行。
SQL> SELECT MAX(al.SEQUENCE#) "Last Seq Recieved",MAX(lh.SEQUENCE#) "Last Seq Applied"
2 FROM v$archived_log al, v$log_history lh;
Last Seq Recieved Last Seq Applied
----------------- ----------------
198 194
SELECT MAX(al.SEQUENCE#) "Last Seq Recieved",MAX(lh.SEQUENCE#) "Last Seq Applied"
FROM v$archived_log al, v$log_history lh;
1.4.9 显示有GAP
SQL> SELECT * FROM v$archive_gap;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 195 196
SQL> SELECT * FROM v$archive_gap;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 195 196
SQL> ALTER DATABASE recover managed standby DATABASE cancel;
数据库已更改。
SQL> SELECT file#,checkpoint_change# FROM v$datafile ORDER BY 1;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1888252
2 1888252
3 1888252
4 1888252
SQL> SELECT file#,checkpoint_change# FROM v$datafile_header ORDER BY 1;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1888992
2 1888992
3 1888992
4 1888992
1.4.10 GAP说明
尽管在恢复两个备份片后 备库日志还说有GAP：
备库归档显示也是缺少归档的：
linux-5pzu:~ # ls -lrt /archive/
total 83144
-rw-r----- 1 oracle oinstall 50181632 Sep 3 10:22 sdk_1_819054888_187.dbf
-rw-r----- 1 oracle oinstall 1024 Sep 3 11:31 sdk_1_819054888_189.dbf
-rw-r----- 1 oracle oinstall 1024 Sep 3 11:31 sdk_1_819054888_191.dbf
-rw-r----- 1 oracle oinstall 2560 Sep 3 11:31 sdk_1_819054888_190.dbf
-rw-r----- 1 oracle oinstall 21905920 Sep 3 11:32 sdk_1_819054888_188.dbf
-rw-r----- 1 oracle oinstall 204288 Sep 3 11:58 sdk_1_819054888_192.dbf
-rw-r----- 1 oracle oinstall 4608 Sep 3 11:58 sdk_1_819054888_193.dbf
-rw-r----- 1 oracle oinstall 3465728 Sep 3 14:11 sdk_1_819054888_194.dbf
缺少删除的196和 196.
-rw-r----- 1 oracle oinstall 7168 Sep 3 14:11 sdk_1_819054888_197.dbf
-rw-r----- 1 oracle oinstall 107520 Sep 3 14:59 sdk_1_819054888_199.dbf
-rw-r----- 1 oracle oinstall 1024 Sep 3 14:59 sdk_1_819054888_200.dbf
-rw-r----- 1 oracle oinstall 398336 Sep 3 14:59 sdk_1_819054888_198.dbf
-rw-r----- 1 oracle oinstall 636928 Sep 3 15:01 sdk_1_819054888_201.dbf
但是实际上已经恢复了 可以同步起来
1.4.11 需要恢复控制文件
SQL> alter database open read only;
alter database open read only
*
第 1 行出现错误:
ORA-16004: 备份数据库需要恢复
ORA-01152: 文件 1 没有从过旧的备份中还原
ORA-01110: 数据文件 1: ‘/oracle/oradata/sdk/system01.dbf‘
Tue Sep 3 14:44:24 2013
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[6]: Assigned to RFS process 5287
RFS[6]: Identified database type as ‘physical standby‘
RFS[6]: Successfully opened standby log 4: ‘/oracle/oradata/sdk/redo1_standby_01‘
Tue Sep 3 14:47:28 2013
ALTER DATABASE recover managed standby DATABASE cancel
Tue Sep 3 14:47:47 2013
MRP0: Background Media Recovery cancelled with status 16037
Tue Sep 3 14:47:47 2013
Errors in file /oracle/admin/sdk/bdump/sdk_mrp0_5277.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Tue Sep 3 14:47:48 2013
Errors in file /oracle/admin/sdk/bdump/sdk_mrp0_5277.trc:
ORA-16037: user requested cancel of managed recovery operation
Tue Sep 3 14:47:48 2013
MRP0: Background Media Recovery process shutdown (sdk)
Tue Sep 3 14:47:49 2013
Managed Standby Recovery Canceled (sdk)
Tue Sep 3 14:47:49 2013
Completed: ALTER DATABASE recover managed standby DATABASE cancel
Tue Sep 3 14:48:25 2013
alter database open read only
Tue Sep 3 14:48:25 2013
Signalling error 1152 for datafile 1!
ORA-16004 signalled during: alter database open read only...
从主库创建控制文件：
SQL> ALTER DATABASE CREATE standby controlfile AS ‘/oracle/standby_lostarchive.ctl‘;
数据库已更改。
SQL> !
oracle@linux-7gnw:~> scp /oracle/standby_lostarchive.ctl oracle@192.168.142.163:/oracle
Password:
standby_lostarchive.ctl 100% 7184KB 7.0MB/s 00:00
1.4.12 备库恢复控制文件
SQL> shutdown immediate
ORA-01109: 数据库未打开
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup nomount
ORACLE 例程已经启动。
Total System Global Area 419430400 bytes
Fixed Size 2084456 bytes
Variable Size 184549784 bytes
Database Buffers 226492416 bytes
Redo Buffers 6303744 bytes
SQL> !
oracle@linux-5pzu:~> rman target /
恢复管理器: Release 10.2.0.4.0 - Production on 星期二 9月 3 14:53:37 2013
Copyright (c) 1982, 2007, Oracle. All rights reserved.
连接到目标数据库: sdk (未装载)
RMAN> restore controlfile from ‘/oracle/standby_lostarchive.ctl‘;
启动 restore 于 03-9月 -13
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: sid=155 devtype=DISK
通道 ORA_DISK_1: 已复制控制文件副本
输出文件名=/oracle/oradata/sdk/control01.ctl
输出文件名=/oracle/oradata/sdk/control02.ctl
输出文件名=/oracle/oradata/sdk/control03.ctl
完成 restore 于 03-9月 -13
RMAN> exit
恢复管理器完成。
1.4.13 启动备库同步
SQL> alter database mount standby database;
数据库已更改。
SQL> alter database recover managed standby database parallel 4 using current logfile disconnect from session;
数据库已更改。
SQL> select process,status,thread#,sequence#,block#,blocks from V$managed_standby;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
------------------ ------------------------ ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
MRP0 WAIT_FOR_LOG 1 198 0 0
RFS IDLE 0 0 0 0
已选择10行。
alter database recover managed standby database parallel 4 using current logfile disconnect from session
Tue Sep 3 14:56:13 2013
Attempt to start background Managed Standby Recovery process (sdk)
MRP0 started with pid=24, OS id=5400
Tue Sep 3 14:56:13 2013
MRP0: Background Managed Standby Recovery process started (sdk)
Managed Standby Recovery starting Real Time Apply
parallel recovery started with 4 processes
Tue Sep 3 14:56:21 2013
Waiting for all non-current ORLs to be archived...
Media Recovery Waiting for thread 1 sequence 198
Tue Sep 3 14:56:21 2013
Completed: alter database recover managed standby database parallel 4 using current logfile disconnect from session
Tue Sep 3 14:57:25 2013
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[1]: Assigned to RFS process 5410
RFS[1]: Identified database type as ‘physical standby‘
Tue Sep 3 14:57:25 2013
RFS LogMiner: Client disabled from further notification
1.5 确认主库同步
1.5.1 主库
SQL> alter system switch logfile;
系统已更改。
SQL> alter system switch logfile;
系统已更改。
SQL> insert into t values(222);
已创建 1 行。
SQL> commit;
提交完成。
SQL> alter system switch logfile;
系统已更改。
SQL> archive log list;
数据库日志模式 存档模式
自动存档 启用
存档终点 /archive
最早的联机日志序列 200
下一个存档日志序列 202
当前日志序列 202
Tue Sep 3 15:01:08 2013
Thread 1 cannot allocate new log, sequence 202
Checkpoint not complete
Current log# 1 seq# 201 mem# 0: /oracle/oradata/sdk/redo01.log
Current log# 1 seq# 201 mem# 1: /oracle/oradata/sdk/redo04.log
Current log# 1 seq# 201 mem# 2: /oracle/oradata/sdk/redo07.log
Tue Sep 3 15:01:09 2013
Thread 1 advanced to log sequence 202 (LGWR switch)
Current log# 2 seq# 202 mem# 0: /oracle/oradata/sdk/redo02.log
Current log# 2 seq# 202 mem# 1: /oracle/oradata/sdk/redo05.log
Current log# 2 seq# 202 mem# 2: /oracle/oradata/sdk/redo08.log
Tue Sep 3 15:01:09 2013
ARC2: Standby redo logfile selected for thread 1 sequence 201 for destination LOG_ARCHIVE_DEST_2
1.5.2 备库
Tue Sep 3 14:59:44 2013
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[2]: Assigned to RFS process 5412
RFS[2]: Identified database type as ‘physical standby‘
RFS[2]: Successfully opened standby log 4: ‘/oracle/oradata/sdk/redo1_standby_01‘
Tue Sep 3 14:59:46 2013
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[3]: Assigned to RFS process 5414
RFS[3]: Identified database type as ‘physical standby‘
RFS[3]: Successfully opened standby log 4: ‘/oracle/oradata/sdk/redo1_standby_01‘
Tue Sep 3 14:59:46 2013
Fetching gap sequence in thread 1, gap sequence 198-198
Tue Sep 3 14:59:46 2013
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[4]: Assigned to RFS process 5416
RFS[4]: Identified database type as ‘physical standby‘
RFS[4]: Archived Log: ‘/archive/sdk_1_819054888_198.dbf‘
Tue Sep 3 15:00:16 2013
Media Recovery Log /archive/sdk_1_819054888_198.dbf
Media Recovery Log /archive/sdk_1_819054888_199.dbf
Media Recovery Log /archive/sdk_1_819054888_200.dbf
Media Recovery Waiting for thread 1 sequence 201
Tue Sep 3 15:01:10 2013
RFS[1]: Successfully opened standby log 4: ‘/oracle/oradata/sdk/redo1_standby_01‘
Tue Sep 3 15:01:12 2013
Media Recovery Log /archive/sdk_1_819054888_201.dbf
Media Recovery Waiting for thread 1 sequence 202
SQL> select process,status,thread#,sequence#,block#,blocks from V$managed_standby;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
------------------ ------------------------ ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CLOSING 1 199 1 209
ARCH CLOSING 1 200 1 1
ARCH CLOSING 1 201 1 1243
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
MRP0 WAIT_FOR_LOG 1 202 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
已选择13行。
1.5.3 查看同步的数据
SQL> ALTER DATABASE recover managed standby DATABASE cancel;
数据库已更改。
SQL> alter database open read only;
数据库已更改。
SQL> select * from t;
ID
----------
111
222
删除的1 2 3 数据同步删除了 新增的222数据也新增了。
1.6 主库执行全备
最后记得将主数据库进行一次全备
OracleDG主库丢失归档增量同步