线上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; 查看归档日志信息
主库日志传不到从库。非常大可能是网络问题或者主从password不一样。
check主备库的password
cd $ORACLE_HOME/dbs
strings orapw[SID]
strings orapwpsdtest
发现主库比从库多了一行nt5L,所以须要统一主库从库的orapwpsdtest password配置文件
直接把主库的password配置文件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
...
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; 查看归档日志信息
主库日志传不到从库。非常大可能是网络问题或者主从password不一样。
check主备库的password
cd $ORACLE_HOME/dbs
strings orapw[SID]
strings orapwpsdtest
发现主库比从库多了一行nt5L,所以须要统一主库从库的orapwpsdtest password配置文件
直接把主库的password配置文件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
这个恢复得到网友团团的协助。在此非常感谢:
參考文章: http://www.eygle.com/archives/2007/08/rman_dataguard_duplicate.html
版权声明:本文博主原创文章,博客,未经同意不得转载。