Heim  >  Artikel  >  Datenbank  >  使用Duplicatetargetdatabase命令恢复线上oracledatagard备库

使用Duplicatetargetdatabase命令恢复线上oracledatagard备库

WBOY
WBOYOriginal
2016-06-07 15:56:511158Durchsuche

线上oracle datagard备库由于断电以及误删除从库的归档日志文件,所以导致,备库主库数据不一致,备库需要紧急恢复,下面是大概恢复过程 1,从主库上面备份控制文件 2,准备备份文件 备份整库,这个步骤可以省去,用今天凌晨3点rman备份好的,而且由于备份目录

线上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
Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn