联系：手机/微信(+86 17813235971) QQ(107644445)
标题：_no_recovery_through_resetlogs参数功能探讨
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
_no_recovery_through_resetlogs参数默认值和描述
SQL> select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
2 from x$ksppi a,x$ksppcv b
3 where a.inst_id = USERENV ('Instance')
4 and b.inst_id = USERENV ('Instance')
5 and a.indx = b.indx
6 and upper(a.ksppinm) LIKE upper('%¶m%')
7 order by name
8 /
Enter value for param: _no_recovery_through_resetlogs
old 6: and upper(a.ksppinm) LIKE upper('%¶m%')
new 6: and upper(a.ksppinm) LIKE upper('%_no_recovery_through_resetlogs%')
NAME VALUE DESCRIPTION
-------------------------------- ------------------------ --------------------------------------------
_no_recovery_through_resetlogs FALSE no recovery through this resetlogs operation
大家知道在10gr2版本及其以后版本,大家知道默认情况下,可以实现跨resetlogs恢复数据库.通过该参数的描述可以看出,该参数的用途是使得resetlogs之后不能继续进行恢复(我的理解是以前的备份不能应用resetlogs后的归档日志)
在实际中该函数的作用是否和该参数的描述相符,我们通过试验验证
rman备份数据库
[oracle@xifenfei tmp]$ rman target / Recovery Manager: Release 10.2.0.4.0 - Production on Thu Aug 30 11:51:49 2012 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database: XFF (DBID=3440302261) RMAN> backup database format '/u01/oracle/oradata/tmp/10g_db_%U'; Starting backup at 30-AUG-12 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=143 devtype=DISK channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00001 name=/u01/oracle/oradata/XFF/system01.dbf input datafile fno=00003 name=/u01/oracle/oradata/XFF/sysaux01.dbf input datafile fno=00002 name=/u01/oracle/oradata/XFF/undotbs01.dbf input datafile fno=00004 name=/u01/oracle/oradata/XFF/users01.dbf channel ORA_DISK_1: starting piece 1 at 30-AUG-12 channel ORA_DISK_1: finished piece 1 at 30-AUG-12 piece handle=/u01/oracle/oradata/tmp/10g_db_0anjuhve_1_1 tag=TAG20120830T115214 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:35 channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset including current control file in backupset including current SPFILE in backupset channel ORA_DISK_1: starting piece 1 at 30-AUG-12 channel ORA_DISK_1: finished piece 1 at 30-AUG-12 piece handle=/u01/oracle/oradata/tmp/10g_db_0bnjui2d_1_1 tag=TAG20120830T115214 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 30-AUG-12
resetlogs打开数据库
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 318767104 bytes Fixed Size 1267236 bytes Variable Size 100665820 bytes Database Buffers 209715200 bytes Redo Buffers 7118848 bytes Database mounted. SQL> recover database until cancel; Media recovery complete. SQL> alter database open resetlogs; Database altered.
创建测试表
SQL> create table t_xifenfei01 2 as 3 select * from dba_tables; Table created. SQL> create table t_xifenfei02 2 as 3 select * from dba_objects; Table created. SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> create table t_xifenfei03 2 as 3 select * from dba_objects; Table created. SQL> alter system switch logfile; System altered. SQL> / System altered.
恢复数据库
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. [oracle@xifenfei XFF]$ rm *.dbf [oracle@xifenfei XFF]$ rman target / Recovery Manager: Release 10.2.0.4.0 - Production on Thu Aug 30 12:00:47 2012 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database (not started) RMAN> startup mount Oracle instance started database mounted Total System Global Area 318767104 bytes Fixed Size 1267236 bytes Variable Size 100665820 bytes Database Buffers 209715200 bytes Redo Buffers 7118848 bytes RMAN> restore database; Starting restore at 30-AUG-12 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=157 devtype=DISK channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00001 to /u01/oracle/oradata/XFF/system01.dbf restoring datafile 00002 to /u01/oracle/oradata/XFF/undotbs01.dbf restoring datafile 00003 to /u01/oracle/oradata/XFF/sysaux01.dbf restoring datafile 00004 to /u01/oracle/oradata/XFF/users01.dbf channel ORA_DISK_1: reading from backup piece /u01/oracle/oradata/tmp/10g_db_0anjuhve_1_1 channel ORA_DISK_1: restored backup piece 1 piece handle=/u01/oracle/oradata/tmp/10g_db_0anjuhve_1_1 tag=TAG20120830T115214 channel ORA_DISK_1: restore complete, elapsed time: 00:00:55 Finished restore at 30-AUG-12 RMAN> recover database; Starting recover at 30-AUG-12 using channel ORA_DISK_1 starting media recovery archive log thread 1 sequence 7 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_7_790743352.dbf archive log thread 1 sequence 8 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_8_790743352.dbf archive log thread 1 sequence 9 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_9_790743352.dbf archive log thread 1 sequence 10 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_10_790743352.dbf archive log thread 1 sequence 1 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_1_792676624.dbf archive log thread 1 sequence 2 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_2_792676624.dbf archive log thread 1 sequence 3 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_3_792676624.dbf archive log thread 1 sequence 4 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_4_792676624.dbf archive log filename=/u01/oracle/oradata/XFF/archivelog/1_7_790743352.dbf thread=1 sequence=7 archive log filename=/u01/oracle/oradata/XFF/archivelog/1_8_790743352.dbf thread=1 sequence=8 archive log filename=/u01/oracle/oradata/XFF/archivelog/1_9_790743352.dbf thread=1 sequence=9 archive log filename=/u01/oracle/oradata/XFF/archivelog/1_10_790743352.dbf thread=1 sequence=10 archive log filename=/u01/oracle/oradata/XFF/archivelog/1_1_792676624.dbf thread=1 sequence=1 archive log filename=/u01/oracle/oradata/XFF/archivelog/1_2_792676624.dbf thread=1 sequence=2 media recovery complete, elapsed time: 00:00:06 Finished recover at 30-AUG-12 SQL> alter database open; Database altered. SQL> select table_name from user_tables where table_name like 'T_XIFENFEI0_'; TABLE_NAME ------------------------------ T_XIFENFEI01 T_XIFENFEI02 T_XIFENFEI03
证明10gr2确实可以跨resetlogs recover 日志恢复数据库
测试_no_recovery_through_resetlogs参数
SQL> create table t_xifenfei04 as 2 select * from dba_objects; Table created. SQL> alter system set "_no_recovery_through_resetlogs"=true scope=spfile; System altered. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 318767104 bytes Fixed Size 1267236 bytes Variable Size 100665820 bytes Database Buffers 209715200 bytes Redo Buffers 7118848 bytes Database mounted. SQL> recover database until cancel; Media recovery complete. SQL> alter database open resetlogs; Database altered. SQL> create table t_xifenfei05 2 as 3 select * from dba_objects; Table created. SQL> create table t_xifenfei06 2 as 3 select * from dba_objects; Table created. SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. [oracle@xifenfei XFF]$ rm *.dbf [oracle@xifenfei XFF]$ rman target / Recovery Manager: Release 10.2.0.4.0 - Production on Thu Aug 30 12:47:40 2012 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database (not started) RMAN> startup mount; Oracle instance started database mounted Total System Global Area 318767104 bytes Fixed Size 1267236 bytes Variable Size 100665820 bytes Database Buffers 209715200 bytes Redo Buffers 7118848 bytes RMAN> restore database; Starting restore at 30-AUG-12 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=157 devtype=DISK channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00001 to /u01/oracle/oradata/XFF/system01.dbf restoring datafile 00002 to /u01/oracle/oradata/XFF/undotbs01.dbf restoring datafile 00003 to /u01/oracle/oradata/XFF/sysaux01.dbf restoring datafile 00004 to /u01/oracle/oradata/XFF/users01.dbf channel ORA_DISK_1: reading from backup piece /u01/oracle/oradata/tmp/10g_db_0anjuhve_1_1 channel ORA_DISK_1: restored backup piece 1 piece handle=/u01/oracle/oradata/tmp/10g_db_0anjuhve_1_1 tag=TAG20120830T115214 channel ORA_DISK_1: restore complete, elapsed time: 00:00:45 Finished restore at 30-AUG-12 RMAN> recover database; Starting recover at 30-AUG-12 using channel ORA_DISK_1 starting media recovery archive log thread 1 sequence 7 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_7_790743352.dbf archive log thread 1 sequence 8 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_8_790743352.dbf archive log thread 1 sequence 9 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_9_790743352.dbf archive log thread 1 sequence 10 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_10_790743352.dbf archive log thread 1 sequence 1 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_1_792676624.dbf archive log thread 1 sequence 2 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_2_792676624.dbf archive log thread 1 sequence 3 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_3_792676624.dbf archive log thread 1 sequence 4 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_4_792676624.dbf archive log thread 1 sequence 5 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_5_792676624.dbf archive log thread 1 sequence 6 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_6_792676624.dbf archive log thread 1 sequence 1 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_1_792679299.dbf archive log thread 1 sequence 2 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_2_792679299.dbf archive log thread 1 sequence 3 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_3_792679299.dbf archive log filename=/u01/oracle/oradata/XFF/archivelog/1_7_790743352.dbf thread=1 sequence=7 archive log filename=/u01/oracle/oradata/XFF/archivelog/1_8_790743352.dbf thread=1 sequence=8 archive log filename=/u01/oracle/oradata/XFF/archivelog/1_9_790743352.dbf thread=1 sequence=9 archive log filename=/u01/oracle/oradata/XFF/archivelog/1_10_790743352.dbf thread=1 sequence=10 --第一次 resetlogs后的归档 archive log filename=/u01/oracle/oradata/XFF/archivelog/1_1_792676624.dbf thread=1 sequence=1 archive log filename=/u01/oracle/oradata/XFF/archivelog/1_2_792676624.dbf thread=1 sequence=2 archive log filename=/u01/oracle/oradata/XFF/archivelog/1_3_792676624.dbf thread=1 sequence=3 archive log filename=/u01/oracle/oradata/XFF/archivelog/1_4_792676624.dbf thread=1 sequence=4 archive log filename=/u01/oracle/oradata/XFF/archivelog/1_5_792676624.dbf thread=1 sequence=5 archive log filename=/u01/oracle/oradata/XFF/archivelog/1_6_792676624.dbf thread=1 sequence=6 --第二次 resetlogs后的归档(设置了_no_recovery_through_resetlogs参数为true并resetlogs后的归档日志 archive log filename=/u01/oracle/oradata/XFF/archivelog/1_1_792679299.dbf thread=1 sequence=1 media recovery complete, elapsed time: 00:00:13 Finished recover at 30-AUG-12 RMAN> alter database open; database opened RMAN> exit Recovery Manager complete. [oracle@xifenfei XFF]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.4.0 - Production on Thu Aug 30 12:49:46 2012 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select table_name from user_tables where table_name like 'T_XIFENFEI0_'; TABLE_NAME ------------------------------ T_XIFENFEI01 T_XIFENFEI02 T_XIFENFEI03 T_XIFENFEI04 T_XIFENFEI05 T_XIFENFEI06 6 rows selected.
通过这里的测试证明使用_no_recovery_through_resetlogs=true后,resetlogs之后还是可以正常可以recover相关日志,证明_no_recovery_through_resetlogs参数不是限制这里的resetlogs后的归档日志应用,至于该参数的具体用途也希望知道的朋友告知下。但是这个参数在clone db和从rac db恢复到单实例db的时候,可能因为bug原因需要设置该参数为true,如:
--rac恢复到单实例RMAN Duplicate from RAC backup fails ORA-38856 [ID 334899.1] sql>alter open database resetlogs; ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled