使用Duplicate target database命令恢复线上oracle datagard备库

转自:http://blog.itpub.net/26230597/viewspace-1174294/

线上oracle datagard备库由于断电以及误删除从库的归档日志文件,所以导致,备库主库数据不一致,备库需要紧急恢复,下面是大概恢复过程

1,从主库上面备份控制文件
[oracle@localhost rman_recover]$ rman target /
RMAN> backup current controlfile for standby format ‘/data/oracle/backup/data/ctlfile.bak’;
Starting backup at 22-MAY-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1094 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
channel ORA_DISK_1: starting piece 1 at 22-MAY-14
channel ORA_DISK_1: finished piece 1 at 22-MAY-14
piece handle=/data/oracle/backup/data/ctlfile.bak tag=TAG20140522T165431 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 22-MAY-14

Starting Control File and SPFILE Autobackup at 22-MAY-14
piece handle=/data/oracle/backup/data/ctl_auto/c-3391761643-20140522-02 comment=NONE
Finished Control File and SPFILE Autobackup at 22-MAY-1
RMAN>

2,准备备份文件
备份整库,这个步骤可以省去,用今天凌晨3点rman备份好的,而且由于备份目录从库也可以直接访问,所以不用scp了。
RMAN> backup database format ‘/u01/rman_recover/%full_backup_%T_%t.bak’;

3,copy主库的备份到备库同样的目录下面。
这个不用了,备份文件在share磁盘里面,主库备库都可以访问得到。

4,然后关闭从库
SQL> shutdown abort
ORACLE instance shut down.
SQL> exit

[oracle@localhost ~]$ ps -eaf|grep oracle
oracle    3137     1  0 May04 ?        00:00:54 /oracle/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr LISTENER -inherit
root     24061 24009  0 15:19 pts/2    00:00:00 su – oracle
oracle   24062 24061  0 15:19 pts/2    00:00:00 -bash
root     24423 22842  0 17:06 pts/0    00:00:00 su – oracle
oracle   24424 24423  0 17:06 pts/0    00:00:00 -bash
oracle   24465 24062  0 17:14 pts/2    00:00:00 ps -eaf
oracle   24466 24062  0 17:14 pts/2    00:00:00 grep oracle
[oracle@localhost ~]$

5,先备份原有的控制文件路径,再copy新的控制文件覆盖备库的控制文件。
5.1备库上查找控制文件路径:
SQL> select name from v$controlfile;
NAME
——————————————————————————–
/home/oradata/psdtest/control01.ctl
/oracle/app/oracle/flash_recovery_area/psdtest/control02.ctl
SQL>

再在备库上copy新的控制文件覆盖原有的控制文件
5.2先备份:
[oracle@localhost ~]$ cp /home/oradata/psdtest/control01.ctl /tmp/control01.ctl.bak
[oracle@localhost ~]$ cp /oracle/app/oracle/flash_recovery_area/psdtest/control02.ctl /tmp/control02.ctl.bak

5.3 再覆盖:
copy控制文件进行覆盖:
cp /data/oracle/backup/data/ctlfile.bak /home/oradata/psdtest/control01.ctl
cp /data/oracle/backup/data/ctlfile.bak /oracle/app/oracle/flash_recovery_area/psdtest/control02.ctl

5.4删除备库归档日志目录下的所有文件
mv /data/oracle/oradgdata/standby_archive/* /data/oracle/backup/data/back_0522_108

6,将备库实例启动到nomount状态。 
启动备库实例:
SQL> startup nomount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              75499764 bytes
Database Buffers           88080384 bytes
Redo Buffers                2973696 bytes

7,获取备库的tns名字,准备用rman登录
7.1,获取备库的tns:(我的是orcl_s.2_tns)
cat $ORACLE_HOME/network/admin/tnsnames.ora
[oracle@localhost data]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
PD_DG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.108)(PORT = 1521))
)
(CONNECT_DATA =
(SID= psdtest)
)
)

PD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.107)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = pdunq)
)
)

PD_DG 这个是你的备库108tns名称,PD就是主库107的tns名称

7.2,再次查看主库SID名称是否统一:
备库: [oracle@localhost archivelog]$ echo $ORACLE_SID
psdtest

主库: [oracle@localhost ~]$ echo $ORACLE_SID
psdtest

7,3 rman 远程登录pd_dg从库库:
再连接
[oracle@localhost ~]$ rlwrap rman target / auxiliary sys/passwdxxx@PD_DG

Recovery Manager: Release 11.2.0.1.0 – Production on Thu May 22 18:11:31 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: psdtest (DBID=3391761643)
connected to auxiliary database: psdtest (not mounted)

RMAN>

7.4,执行恢复命令
run {
allocate auxiliary channel c1 device type disk;
allocate auxiliary channel c2 device type disk;
duplicate target database for standby nofilenamecheck dorecover;
release channel c1;
release channel c2;
}
#nofilenamecheck:必须指定NOFILENAMECHECK参数,避免覆盖primary数据库的当前的数据文件。另外主从路径一致,不需要执行文件路径以及check了
#release :这是关闭 前两行开启的通道

开始执行:
RMAN>run {
allocate auxiliary channel c1 device type disk;
allocate auxiliary channel c2 device type disk;
duplicate target database for standby nofilenamecheck dorecover;
release channel c1;
release channel c2;
3> 4> 5> 6> 7> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=386 device type=DISK

allocated channel: c2
channel c2: SID=482 device type=DISK

Starting Duplicate Db at 23-MAY-14

contents of Memory Script:
{
set until scn  10436786792;
restore clone standby controlfile;
}
executing Memory Script
executing command: SET until clause
Starting restore at 23-MAY-14
channel c1: starting datafile backup set restore
channel c1: restoring control file
channel c1: reading from backup piece /data/oracle/backup/data/ctl_auto/c-3391761643-20140523-00
channel c1: piece handle=/data/oracle/backup/data/ctl_auto/c-3391761643-20140523-00 tag=TAG20140523T033551
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:01
output file name=/home/oradata/psdtest/control01.ctl
output file name=/oracle/app/oracle/flash_recovery_area/psdtest/control02.ctl
Finished restore at 23-MAY-14

contents of Memory Script:
{
sql clone ‘alter database mount standby database’;
}
executing Memory Script
sql statement: alter database mount standby databas
contents of Memory Script:
{
set until scn  10436786792;
set newname for tempfile  1 to
“/home/oradata/psdtest/temp01.dbf”;
switch clone tempfile all;
set newname for datafile  1 to
“/home/oradata/psdtest/system01.dbf”;
set newname for datafile  2 to
“/home/oradata/psdtest/sysaux01.dbf”;
set newname for datafile  3 to
“/home/oradata/psdtest/undotbs01.dbf”;
set newname for datafile  4 to
“/home/oradata/psdtest/users01.dbf”;
set newname for datafile  5 to
“/home/oradata/psdtest/psdtestk01.dbf”;
set newname for datafile  6 to
“/home/oradata/psdtest/plas01.dbf”;
set newname for datafile  7 to
“/home/oradata/psdtest/pl01.dbf”;
set newname for datafile  8 to
“/home/oradata/psdtest/help01.dbf”;
set newname for datafile  9 to
“/home/oradata/psdtest/adobelc01.dbf”;
restore
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
renamed tempfile 1 to /home/oradata/psdtest/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 23-MAY-14
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00001 to /home/oradata/psdtest/system01.dbf
channel c1: restoring datafile 00002 to /home/oradata/psdtest/sysaux01.dbf
channel c1: restoring datafile 00003 to /home/oradata/psdtest/undotbs01.dbf
channel c1: restoring datafile 00004 to /home/oradata/psdtest/users01.dbf
channel c1: restoring datafile 00005 to /home/oradata/psdtest/psdtestk01.dbf
channel c1: restoring datafile 00006 to /home/oradata/psdtest/plas01.dbf
channel c1: restoring datafile 00007 to /home/oradata/psdtest/pl01.dbf
channel c1: restoring datafile 00008 to /home/oradata/psdtest/help01.dbf
channel c1: restoring datafile 00009 to /home/oradata/psdtest/adobelc01.dbf
channel c1: reading from backup piece /data/oracle/backup/data/2014-05-23/full_psdtest_20140523_1304.bak
channel c1: piece handle=/data/oracle/backup/data/2014-05-23/full_psdtest_20140523_1304.bak tag=TAG20140523T032104
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:20:15
Finished restore at 23-MAY-14
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=848313777 file name=/home/oradata/psdtest/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=848313777 file name=/home/oradata/psdtest/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=848313777 file name=/home/oradata/psdtest/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=848313777 file name=/home/oradata/psdtest/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=5 STAMP=848313777 file name=/home/oradata/psdtest/psdtestk01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=6 STAMP=848313777 file name=/home/oradata/psdtest/plas01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=7 STAMP=848313777 file name=/home/oradata/psdtest/pl01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=8 STAMP=848313777 file name=/home/oradata/psdtest/help01.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=9 STAMP=848313778 file name=/home/oradata/psdtest/adobelc01.dbf

contents of Memory Script:
{
set until scn  10436786792;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 23-MAY-14
starting media recovery
released channel: c1
released channel: c2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 05/23/2014 10:44:21
RMAN-03015: error occurred in stored script Memory Script
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 8254 and starting SCN of 10436680710 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 8253 and starting SCN of 10436636473 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 8252 and starting SCN of 10436621186 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 8251 and starting SCN of 10436616182 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 8250 and starting SCN of 10436611843 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 8249 and starting SCN of 10436606580 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 8248 and starting SCN of 10436589966 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 8247 and starting SCN of 10436589919 found to restore
有报错信息,查看alert日志信息:
贴一下从库的alert日志信息:
[oracle@localhost data]$   tail -f /oracle/app/oracle/diag/rdbms/pddgunq/psdtest/trace/alert_psdtest.log
[oracle@localhost standby_archive]$ history |grep tail
804  tail -f /oracle/app/oracle/diag/rdbms/pddgunq/psdtest/trace/alert_psdtest.log
813    tail -f /oracle/app/oracle/diag/rdbms/pddgunq/psdtest/trace/alert_psdtest.log
878  history |grep tail
[oracle@localhost standby_archive]$ tail -f /oracle/app/oracle/diag/rdbms/pddgunq/psdtest/trace/alert_psdtest.log
Errors in file /oracle/app/oracle/diag/rdbms/pddgunq/psdtest/trace/psdtest_arc2_478.trc:
ORA-16191: Primary log shipping client not logged on standby
ARCj: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Fri May 23 10:22:45 2014
Full restore complete of datafile 8 /home/oradata/psdtest/help01.dbf.  Elapsed time: 0:00:02
checkpoint is 10436588043
last deallocation scn is 9881798870
Full restore complete of datafile 9 /home/oradata/psdtest/adobelc01.dbf.  Elapsed time: 0:00:00
checkpoint is 10436588043
Fri May 23 10:23:25 2014
Full restore complete of datafile 7 /home/oradata/psdtest/pl01.dbf.  Elapsed time: 0:00:26
checkpoint is 10436588043
last deallocation scn is 10430929064
Fri May 23 10:24:22 2014
Full restore complete of datafile 3 /home/oradata/psdtest/undotbs01.dbf.  Elapsed time: 0:01:19
checkpoint is 10436588043
last deallocation scn is 10436580283
Undo Optimization current scn is 10436537601
Fri May 23 10:25:34 2014
Full restore complete of datafile 4 /home/oradata/psdtest/users01.dbf.  Elapsed time: 0:02:52
checkpoint is 10436588043
last deallocation scn is 10431120328
Fri May 23 10:27:10 2014
Full restore complete of datafile 1 /home/oradata/psdtest/system01.dbf.  Elapsed time: 0:04:06
checkpoint is 10436588043
last deallocation scn is 10431182430
Undo Optimization current scn is 10436537601
Fri May 23 10:30:07 2014
Full restore complete of datafile 6 /home/oradata/psdtest/plas01.dbf.  Elapsed time: 0:07:23
checkpoint is 10436588043
last deallocation scn is 10431118551
Fri May 23 10:30:30 2014
Full restore complete of datafile 2 /home/oradata/psdtest/sysaux01.dbf.  Elapsed time: 0:07:46
checkpoint is 10436588043
last deallocation scn is 10436477741
Fri May 23 10:36:02 2014
db_recovery_file_dest_size of 15360 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Fri May 23 10:42:47 2014
Full restore complete of datafile 5 /home/oradata/psdtest/psdtestk01.dbf.  Elapsed time: 0:19:52
checkpoint is 10436588043
last deallocation scn is 10431121555
Fri May 23 10:42:58 2014
Switch of datafile 1 complete to datafile copy
checkpoint is 10436588043
Switch of datafile 2 complete to datafile copy
checkpoint is 10436588043
Switch of datafile 3 complete to datafile copy
checkpoint is 10436588043
Switch of datafile 4 complete to datafile copy
checkpoint is 10436588043
Switch of datafile 5 complete to datafile copy
checkpoint is 10436588043
Switch of datafile 6 complete to datafile copy
checkpoint is 10436588043
Switch of datafile 7 complete to datafile copy
checkpoint is 10436588043
Switch of datafile 8 complete to datafile copy
checkpoint is 10436588043
Switch of datafile 9 complete to datafile copy
checkpoint is 10436588043
Using STANDBY_ARCHIVE_DEST parameter default value as /data/oracle/oradgdata/standby_archive
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover datafile list
1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9
Completed: alter database recover datafile list
1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9
alter database recover if needed
standby start until change 10436786792
Media Recovery Start
started logmerger process
Fri May 23 10:43:00 2014
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 16 slaves
ORA-279 signalled during: alter database recover if needed
standby start until change 10436786792

Fri May 23 10:43:22 2014
alter database recover cancel
Fri May 23 10:43:22 2014
Signalling error 1152 for datafile 1!
Errors in file /oracle/app/oracle/diag/rdbms/pddgunq/psdtest/trace/psdtest_pr00_676.trc:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: ‘/home/oradata/psdtest/system01.dbf’
Slave exiting with ORA-1547 exception
Errors in file /oracle/app/oracle/diag/rdbms/pddgunq/psdtest/trace/psdtest_pr00_676.trc:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: ‘/home/oradata/psdtest/system01.dbf’
ORA-10879 signalled during: alter database recover cancel…

8,看到报错了,去备库,看看实例状态是否为MOUNTED
select status from v$instance;
SQL> select status from v$instance;
STATUS
————
MOUNTED

SQL>
SQL> select name,applied from v$archived_log;
no rows selected
SQL>

再查看下日志归档到哪里了:
从库上:
SQL> archive log list
Database log mode       Archive Mode
Automatic archival       Enabled
Archive destination       /data/oracle/oradgdata/standby_archive
Oldest online log sequence     8245
Next log sequence to archive   8247
Current log sequence       8247
SQL>
主库上:
SQL> archive log list
Database log mode              Archive Mode
Automatic archival       Enabled
Archive destination       /oracle/app/oracle/flash_recovery_area/archivelog
Oldest online log sequence     8255
Next log sequence to archive   8257
Current log sequence       8257
SQL>
主库从库相差10个archive log点。
主库上执行:
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#);

查看备份库的tns信息如下:
cat $ORACLE_HOME/network/admin/tnsnames.ora
备库上验证tns
[oracle@localhost standby_archive]$ sqlplus sys/passwdxxx@PD as sysdba;
SQL*Plus: Release 11.2.0.1.0 Production on Fri May 23 11:17:51 2014

Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL>
可以登录,证明tns是正常的。

备库上再执行:
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: ‘/home/oradata/psdtest/system01.dbf’

查看主库备库的scn号码:
SQL> select to_char(current_scn) from v$database;
TO_CHAR(CURRENT_SCN)
—————————————-
10436589934

SQL> select to_char(current_scn) from v$database;
TO_CHAR(CURRENT_SCN)
—————————————-
10437053152

9,上一步中,如果为MOUNTED,则可以开始启动备库的REDO应用,去从库执行:
alter database recover managed standby database disconnect from session;
备库执行:
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL>

SQL> select name,applied from v$archived_log;
no rows selected
SQL> archive log list;
Database log mode       Archive Mode
Automatic archival       Enabled
Archive destination       /data/oracle/oradgdata/standby_archive
Oldest online log sequence     0
Next log sequence to archive   0
Current log sequence       0
SQL>

主库从库执行:
select dbms_flashback.get_system_change_number from dual
SQL>  select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
————————
1.0436E+10

SQL>
备库执行:
SQL> select dbms_flashback.get_system_change_number from dual;
select dbms_flashback.get_system_change_number from dual
*
ERROR at line 1:
ORA-00904: “DBMS_FLASHBACK”.”GET_SYSTEM_CHANGE_NUMBER”: invalid identifier
SQL>

总结原因,问了一些前辈:
从ORA-01152: file 1 was not restored from a sufficiently old backup,推测是因为使用的控制文件是今天当前的,
而duplicate target databas执行的最新备份是凌晨3点执行的全备,所以报错,他们建议等明天重新使用今天的控制文件
执行duplicate target databas看看。

10,等到第二天,用前一天的控制文件,重新执行恢复,步骤如下:
(1) 停止备库:
shutdown abort

(2) 备库直接copy控制文件覆盖:
cp /data/oracle/backup/data/ctlfile.bak /home/oradata/psdtest/control01.ctl
cp /data/oracle/backup/data/ctlfile.bak /oracle/app/oracle/flash_recovery_area/psdtest/control02.ctl

(3) 启动备库到nomount
startup nomount

(4) 主库登入rman,执行恢复
rlwrap rman target / auxiliary sys/passwdxxx@PD_DG
执行:
run {
allocate auxiliary channel c1 device type disk;
allocate auxiliary channel c2 device type disk;
duplicate target database for standby nofilenamecheck dorecover;
release channel c1;
release channel c2;
}
…….
一切正常顺利

(5) 看主库alert日志,有报错信息:
[oracle@localhost ~]$ tail -f /oracle/app/oracle/diag/rdbms/pdunq/psdtest/trace/alert_psdtest.log
FAL[server, ARC4]: FAL archive failed, see trace file.
Errors in file /oracle/app/oracle/diag/rdbms/pdunq/psdtest/trace/psdtest_arc4_3253.trc:
ORA-16055: FAL request rejected
ARCH: FAL archive failed. Archiver continuing
ORACLE Instance psdtest – Archival Error. Archiver continuing.
Fri May 23 15:54:21 2014
Error 1031 received logging on to the standby
Errors in file /oracle/app/oracle/diag/rdbms/pdunq/psdtest/trace/psdtest_arc1_3247.trc:
ORA-01031: insufficient privileges
PING[ARC1]: Heartbeat failed to connect to standby ‘PD_DG’. Error is 1031.

check从库归档日志,没有新的变化,
SQL> archive log list
Database log mode       Archive Mode
Automatic archival       Enabled
Archive destination       /data/oracle/oradgdata/standby_archive
Oldest online log sequence     8245
Next log sequence to archive   8247
Current log sequence       8247

去从库执行日志切换,看看主库上新产生的告警信息:
alter system switch logfile;

archive log list; 查看归档日志信息
主库日志传不到从库,很大可能是网络问题或者主从密码不一样。
check主备库的密码
cd $ORACLE_HOME/dbs
strings orapw[SID]
strings orapwpsdtest
发现主库比从库多了一行nt5L,所以需要统一主库从库的orapwpsdtest 密码配置文件
直接把主库的密码配置文件cp过去就可以了。

然后去主库执行:alter system set log_archive_dest_state_2=enable;执行完了再check scn
主库:
SQL> select to_char(current_scn) from v$database;

TO_CHAR(CURRENT_SCN)
—————————————-
10437558617

备库:
SQL> select to_char(current_scn) from v$database;

TO_CHAR(CURRENT_SCN)
—————————————-
10436589934
看到主库备库的SCN还是有差距的。

用select name,applied from v$archived_log; 命令查看主库备库归档日志是否都已经统一了,备库上面归档日志是否都已经传过来了。

开始应用日志
alter database recover managed standby database disconnect from session;
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL>

继续查询主库备库的scn,看到备库一直在增加,就表示应用日志已经开始起作用了:
select to_char(current_scn) from v$database;
SQL> select to_char(current_scn) from v$database;

TO_CHAR(CURRENT_SCN)
—————————————-
10437099045

SQL> select to_char(current_scn) from v$database;

TO_CHAR(CURRENT_SCN)
—————————————-
10437257415

SQL>

再继续执行select name,applied from v$archived_log命令观察从库,当从库上都是YES的时候,证明归档日志全部传过来了。
当从库上applied全部为YES的时候,执行如下,取消应用(不取消不让打开数据库)
alter database recover managed standby database cancel;
然后alter database open;
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open;

Database altered.
SQL>

看看mode是:
SQL> select database_role,open_mode from v$database;

DATABASE_ROLE OPEN_MODE
—————- ——————–
PHYSICAL STANDBY READ ONLY

如果是read only模式,那么启动应用就好了。
再次启动redo
alter database recover managed standby database disconnect from session;
SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL>
再查看mode模式,就是READ ONLY WITH APPLY了。
SQL> select database_role,open_mode from v$database;

DATABASE_ROLE OPEN_MODE
—————- ——————–
PHYSICAL STANDBY READ ONLY WITH APPLY

SQL>
再执行 select name,applied from v$archived_log; 都是YES了。证明主库备库数据已经完全一致了。

11,附带:删除以前的旧的归档日志:
先移除掉日志:

mv /oracle/app/oracle/flash_recovery_area/archivelog/1_75* /data/oracle/backup/data/archivelog/tmp_bak_2014/
mv /oracle/app/oracle/flash_recovery_area/archivelog/1_76* /data/oracle/backup/data/archivelog/tmp_bak_2014/
mv /oracle/app/oracle/flash_recovery_area/archivelog/1_77* /data/oracle/backup/data/archivelog/tmp_bak_2014/
mv /oracle/app/oracle/flash_recovery_area/archivelog/1_78* /data/oracle/backup/data/archivelog/tmp_bak_2014/
mv /oracle/app/oracle/flash_recovery_area/archivelog/1_79* /data/oracle/backup/data/archivelog/tmp_bak_2014/
mv /oracle/app/oracle/flash_recovery_area/archivelog/1_80* /data/oracle/backup/data/archivelog/tmp_bak_2014/
mv /oracle/app/oracle/flash_recovery_area/archivelog/1_81* /data/oracle/backup/data/archivelog/tmp_bak_2014/

(1) 登录:
rlwrap rman target /
(2) 检查:crosscheck archivelog all;
crosscheck archivelog all:验证的是DB的归档日志即log_archive_dest参数指定位置的文件,当手工删除了归档日志以后,
Rman备份会检测到日志缺失,从而无法进一步继续执行Rman备份,所以此时需要手工执行crosscheck过程,查看所有的归档日
志文件是否都是正常的然后再来执行Rman备份。
….
validation succeeded for archived log
archived log file name=/oracle/app/oracle/flash_recovery_area/archivelog/1_8272_821708334.dbf RECID=15437 STAMP=848338357
validation succeeded for archived log
archived log file name=/oracle/app/oracle/flash_recovery_area/archivelog/1_8273_821708334.dbf RECID=15439 STAMP=848338718
validation succeeded for archived log
archived log file name=/oracle/app/oracle/flash_recovery_area/archivelog/1_8274_821708334.dbf RECID=15441 STAMP=848339078
validation succeeded for archived log
archived log file name=/oracle/app/oracle/flash_recovery_area/archivelog/1_8275_821708334.dbf RECID=15443 STAMP=848339439
validation succeeded for archived log
archived log file name=/oracle/app/oracle/flash_recovery_area/archivelog/1_8276_821708334.dbf RECID=15445 STAMP=848339802
Crosschecked 720 objects

(3) delete expired archivelog all;
这时候我们再去OEM中就看不到这些日志文件了,如果你的从来没有做过这个动作的话,我们可以比较这个动作前的controlfile
和动作后的controlfile的文件大小。
参考: http://mingyue19850801.blog.163.com/blog/static/19520820201162233314720/

(4) 主库上面SQL模式下执行:
alter system set log_archive_dest_state_3=defer;
这个是检查日志的,通过检查日志rman能标记处已经废弃的日志,接下来才可以删除被标记的日志
SQL> alter system set log_archive_dest_state_3=defer;
System altered.
SQL>

(5) 备库上sql模式执行;
SQL> archive log list
Database log mode       Archive Mode
Automatic archival       Enabled
Archive destination       /data/oracle/oradgdata/standby_archive
Oldest online log sequence     8276
Next log sequence to archive   0
Current log sequence       8278
SQL>

多执行几次,归档号码会变化
SQL> archive log list
Database log mode       Archive Mode
Automatic archival       Enabled
Archive destination       /data/oracle/oradgdata/standby_archive
Oldest online log sequence     8276
Next log sequence to archive   0
Current log sequence       8279

(6) 主库上sql模式下执行:
ALTER SYSTEM SET log_archive_dest_3=”;
SQL> ALTER SYSTEM SET log_archive_dest_3=”;
System altered.
SQL>
刚才我们不是添加的dest_3的归档吗,现在dest_2就可以了,不需要这个了,所以把这个置为空

(7) 主库sql模式下切换日志
alter system switch logfile;
SQL> alter system switch logfile;
System altered.
SQL>
然后去从库上看日志号码,变成8280了。
SQL> archive log list
Database log mode       Archive Mode
Automatic archival       Enabled
Archive destination       /data/oracle/oradgdata/standby_archive
Oldest online log sequence     8277
Next log sequence to archive   0
Current log sequence       8280

去从库sql模式下查看 select name,applied from v$archived_log;都是YES
……
NAME
——————————————————————————–
APPLIED
———
/data/oracle/oradgdata/standby_archive/1_8276_821708334.dbf
YES

/data/oracle/oradgdata/standby_archive/1_8277_821708334.dbf
YES

/data/oracle/oradgdata/standby_archive/1_8278_821708334.dbf
YES

33 rows selected.
主库上是NO是正常的,因为它不需要应用日志,日志就是它自己产生的,只要从库上是YES就可以了。

然后再去主库上操作:
主库上执行sql:
alter system set log_archive_dest_state_3=enable;
SQL> alter system set log_archive_dest_state_3=enable;
System altered.

然后查看archive参数
SQL> show parameter archive
可以看到如下信息

log_archive_dest_state_1     string ENABLE

log_archive_dest_state_2     string ENABLE

log_archive_dest_state_3     string ENABLE

3个通道都是打开的。OK了。
这个恢复得到网友团团的协助,在此非常感谢:
参考文章:  http://www.eygle.com/archives/2007/08/rman_dataguard_duplicate.html

发表评论

电子邮件地址不会被公开。