问:冷备数据库能否追溯应用旧库归档日志呢?通过实验来寻找答案!
答案是不可以!下面我们通过做实验来证明:
先做数据库冷备并迁移至新目录,旧库保留:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
数据库当前物理结构:
SQL> select name from v$controlfile
2 union all
3 select name from v$datafile
4 union all
5 select member from v$logfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/product/10.2.0/db_1/dbs/cntrlora10g.dbf
/backup/copy/copy_ORA10G_data_D-ORA10G_I-4160719624_TS-SYSTEM_FNO-1_2hpbolmn
/backup/copy/copy_ORA10G_data_D-ORA10G_I-4160719624_TS-UNDOTBS1_FNO-2_2gpbolmn
/backup/copy/copy_ORA10G_data_D-ORA10G_I-4160719624_TS-SYSAUX_FNO-3_2ipbolmn
/backup/copy/copy_ORA10G_data_D-ORA10G_I-4160719624_TS-TSA_FNO-5_2jpbolmn
/backup/copy/copy_ORA10G_data_D-ORA10G_I-4160719624_TS-TSB_FNO-6_2kpbolmo
/backup/copy/copy_ORA10G_data_D-ORA10G_I-4160719624_TS-TSC_FNO-7_2lpbolnh
/backup/copy/copy_ORA10G_data_D-ORA10G_I-4160719624_TS-TSD_FNO-8_2mpbolnh
/backup/copy/redo07.log
/backup/copy/redo08.log
/backup/copy/redo09.log
SQL> show parameter pfile;
NAME TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
spfile string
/u01/app/oracle/product/10.2.0
/db_1/dbs/spfileora10g.ora
SQL> create pfile='/tmp/new.ora' from spfile;
File created.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/10.2.0/db_1/dbs/arch
Oldest online log sequence 14
Next log sequence to archive 16
Current log sequence 16
SQL> select * from t;
I D
---------- ---------------
1 05-JUL-14
SQL> insert into t values(2,sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t;
I D
---------- ---------------
1 05-JUL-14
2 27-SEP-14 --插入一行测试数据,共两行数据
冷备数据库
1. 正常关闭数据库,并将数据库的物理文件(控制文件,数据文件,在线日志文件)都拷贝至新的目录下/u01/app/oracle/oradata/newdb下
mkdir /arch/newdb
mkdir -p /u01/app/oracle/oradata/newdb
cp -rp /u01/app/oracle/product/10.2.0/db_1/dbs/cntrlora10g.dbf /u01/app/oracle/oradata/newdb/cntrlora10g.dbf
cp -rp /backup/copy/copy_ORA10G_data_D-ORA10G_I-4160719624_TS-SYSTEM_FNO-1_2hpbolmn /u01/app/oracle/oradata/newdb/SYSTEM01.dbf
cp -rp /backup/copy/copy_ORA10G_data_D-ORA10G_I-4160719624_TS-UNDOTBS1_FNO-2_2gpbolmn /u01/app/oracle/oradata/newdb/UNDOTBS101.dbf
cp -rp /backup/copy/copy_ORA10G_data_D-ORA10G_I-4160719624_TS-SYSAUX_FNO-3_2ipbolmn /u01/app/oracle/oradata/newdb/SYSAUX01.dbf
cp -rp /backup/copy/copy_ORA10G_data_D-ORA10G_I-4160719624_TS-TSA_FNO-5_2jpbolmn /u01/app/oracle/oradata/newdb/TSA01.dbf
cp -rp /backup/copy/copy_ORA10G_data_D-ORA10G_I-4160719624_TS-TSB_FNO-6_2kpbolmo /u01/app/oracle/oradata/newdb/TSB01.dbf
cp -rp /backup/copy/copy_ORA10G_data_D-ORA10G_I-4160719624_TS-TSC_FNO-7_2lpbolnh /u01/app/oracle/oradata/newdb/TSC01.dbf
cp -rp /backup/copy/copy_ORA10G_data_D-ORA10G_I-4160719624_TS-TSD_FNO-8_2mpbolnh /u01/app/oracle/oradata/newdb/TSD01.dbf
cp -rp /backup/copy/redo07.log /u01/app/oracle/oradata/newdb/redo07.log
cp -rp /backup/copy/redo08.log /u01/app/oracle/oradata/newdb/redo08.log
cp -rp /backup/copy/redo09.log /u01/app/oracle/oradata/newdb/redo09.log
2. 修改/tmp/new.ora参数文件里的两个参数,控制文件路径和归档目录
control_files='/u01/app/oracle/oradata/newdb/cntrlora10g.dbf'
log_archive_dest_1='location=/arch/newdb'
3. 启动新数据库至mount状态,修改数据库库文件至新路径,并打开数据库
[oracle@tivoli02 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Sat Sep 27 01:59:46 2014
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup mount pfile='/tmp/new.ora';
ORA-32006: SQL_TRACE initialization parameter has been deprecated
ORACLE instance started.
Total System Global Area 260046848 bytes
Fixed Size 2095472 bytes
Variable Size 113247888 bytes
Database Buffers 134217728 bytes
Redo Buffers 10485760 bytes
Database mounted.
SQL> alter database rename file '/backup/copy/copy_ORA10G_data_D-ORA10G_I-4160719624_TS-SYSTEM_FNO-1_2hpbolmn' to '/u01/app/oracle/oradata/newdb/SYSTEM01.dbf';
SQL> alter database rename file '/backup/copy/copy_ORA10G_data_D-ORA10G_I-4160719624_TS-UNDOTBS1_FNO-2_2gpbolmn' to '/u01/app/oracle/oradata/newdb/UNDOTBS101.dbf';
SQL> alter database rename file '/backup/copy/copy_ORA10G_data_D-ORA10G_I-4160719624_TS-SYSAUX_FNO-3_2ipbolmn' to '/u01/app/oracle/oradata/newdb/SYSAUX01.dbf';
SQL> alter database rename file '/backup/copy/copy_ORA10G_data_D-ORA10G_I-4160719624_TS-TSA_FNO-5_2jpbolmn' to '/u01/app/oracle/oradata/newdb/TSA01.dbf';
SQL> alter database rename file '/backup/copy/copy_ORA10G_data_D-ORA10G_I-4160719624_TS-TSB_FNO-6_2kpbolmo' to '/u01/app/oracle/oradata/newdb/TSB01.dbf';
SQL> alter database rename file '/backup/copy/copy_ORA10G_data_D-ORA10G_I-4160719624_TS-TSC_FNO-7_2lpbolnh' to '/u01/app/oracle/oradata/newdb/TSC01.dbf';
SQL> alter database rename file '/backup/copy/copy_ORA10G_data_D-ORA10G_I-4160719624_TS-TSD_FNO-8_2mpbolnh' to '/u01/app/oracle/oradata/newdb/TSD01.dbf';
SQL> alter database rename file '/backup/copy/redo07.log' to '/u01/app/oracle/oradata/newdb/redo07.log';
SQL> alter database rename file '/backup/copy/redo08.log' to '/u01/app/oracle/oradata/newdb/redo08.log';
SQL> alter database rename file '/backup/copy/redo09.log' to '/u01/app/oracle/oradata/newdb/redo09.log';
SQL> select name from v$controlfile
union all
select name from v$datafile
union all
select member from v$logfile;
2 3 4 5
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/newdb/cntrlora10g.dbf
/u01/app/oracle/oradata/newdb/SYSTEM01.dbf
/u01/app/oracle/oradata/newdb/UNDOTBS101.dbf
/u01/app/oracle/oradata/newdb/SYSAUX01.dbf
/u01/app/oracle/oradata/newdb/TSA01.dbf
/u01/app/oracle/oradata/newdb/TSB01.dbf
/u01/app/oracle/oradata/newdb/TSC01.dbf
/u01/app/oracle/oradata/newdb/TSD01.dbf
/u01/app/oracle/oradata/newdb/redo07.log
/u01/app/oracle/oradata/newdb/redo08.log
/u01/app/oracle/oradata/newdb/redo09.log
SQL> alter database open;
Database altered.
SQL> select * from t;
I D
---------- ---------------
1 05-JUL-14
2 27-SEP-14 --测试数据完整,迁移顺利结束
至此,最简单的数据库迁移就完成了!
验证:新库能否应用旧库的归档日志
下面我们来验证一下新库能否应用旧库的归档日志.
步骤:
1) 旧库添加一条新数据并且切换在线日志
2) 将刚切出来的在线日志注册至新数据库的控制文件中,并尝试恢复来打开数据库
旧库:
SQL> insert into t values(3,sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t;
I D
---------- ---------------
1 05-JUL-14
2 27-SEP-14
3 27-SEP-14 <span style="color:#ff0000;">--插入新的测试数据</span>
SQL> alter system archive log current;
SQL> alter system archive log current;
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/10.2.0/db_1/dbs/arch
Oldest online log sequence 15
Next log sequence to archive 17
Current log sequence 17
SQL> shutdown abort;
ORACLE instance shut down.
新库:
[oracle@tivoli02 newdb]$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Sat Sep 27 02:25:59 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup mount pfile='/tmp/new.ora';
Oracle instance started
database mounted
Total System Global Area 260046848 bytes
Fixed Size 2095472 bytes
Variable Size 113247888 bytes
Database Buffers 134217728 bytes
Redo Buffers 10485760 bytes
RMAN>
RMAN> catalog archivelog '/u01/app/oracle/product/10.2.0/db_1/dbs/arch1_16_857999924.dbf'; --注册归档日志
using target database control file instead of recovery catalog
cataloged archive log
archive log filename=/u01/app/oracle/product/10.2.0/db_1/dbs/arch1_16_857999924.dbf recid=260 stamp=859343275
RMAN> recover database;
Starting recover at 27-SEP-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=33 devtype=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 27-SEP-14
RMAN> alter database open;
database opened
SQL> select * from t;
I D
---------- ---------------
1 05-JUL-14
2 27-SEP-14 --新库在恢复数据库以后仍然是两条测试数据
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /arch/newdb
Oldest online log sequence 16
Next log sequence to archive 18
<span style="color:#ff0000;">Current log sequence 18</span>
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/newdb/cntrlora10g.dbf
测试数据依旧为两条,日志序列号加1,可见尽管已经把归档日志注册至新数据库,但依然无法从归档日志恢复数据库
停新库,启旧库
SQL> shutdown abort;
ORACLE instance shut down.
SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup mount;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 260046848 bytes
Fixed Size 2095472 bytes
Variable Size 113247888 bytes
Database Buffers 134217728 bytes
Redo Buffers 10485760 bytes
Database mounted.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/10.2.0/db_1/dbs/arch
Oldest online log sequence 15
Next log sequence to archive 17
Current log sequence 17
SQL> recover database;
Media recovery complete.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/10.2.0/db_1/dbs/arch
Oldest online log sequence 15
Next log sequence to archive 17
Current log sequence 17
SQL> alter database open;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/10.2.0/db_1/dbs/arch
Oldest online log sequence 16
Next log sequence to archive 18
Current log sequence 18
可见,因为每次RECOVER DATABASE之后再打开数据库,都会做一次在线日志切换
旧库插入新测试数据,关闭旧库,启新库
将旧库的归档日志替换新库的当前在线日志,再一次尝试恢复或打开数据库
尝试去将序列号为18的当前在线日志换成旧库归档日志,数据库仍然完成恢复或完成打开!
SQL> insert into t values(4,sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system archive log current;
System altered.
SQL> shutdown abort;
ORACLE instance shut down.
SQL> connect /as sysdba
Connected to an idle instance.
SQL> startup mount pfile='/tmp/new.ora';
ORA-32006: SQL_TRACE initialization parameter has been deprecated
ORACLE instance started.
Total System Global Area 260046848 bytes
Fixed Size 2095472 bytes
Variable Size 113247888 bytes
Database Buffers 134217728 bytes
Redo Buffers 10485760 bytes
Database mounted.
SQL> set linesize 300;
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ---------------
7 1 16 52428800 1 YES INACTIVE 13078160 26-SEP-14
9 1 18 52428800 1 NO CURRENT 13129023 27-SEP-14
8 1 17 52428800 1 YES INACTIVE 13108872 27-SEP-14
SQL> !
[oracle@tivoli02 newdb]$ pwd
/u01/app/oracle/oradata/newdb
[oracle@tivoli02 newdb]$ mv redo09.log redo09.log.bak
</span>[oracle@tivoli02 newdb]$ cp -rp /u01/app/oracle/product/10.2.0/db_1/dbs/arch1_18_857999924.dbf ./redo09.log
[oracle@tivoli02 newdb]$ exit
exit
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00327: log 9 of thread 1, physical size 1273 less than needed 102400
ORA-00312: online log 9 thread 1: '/u01/app/oracle/oradata/newdb/redo09.log'
SQL> recover database ;
ORA-00283: recovery session canceled due to errors
ORA-00327: log 9 of thread 1, physical size 1273 less than needed 102400
ORA-00312: online log 9 thread 1: '/u01/app/oracle/oradata/newdb/redo09.log'
[oracle@tivoli02 bdump]$ oerr ora 00327
00327, 00000, "log %s of thread %s, physical size %s less than needed %s"
// *Cause: A log file has shrunk in size. This is likely
// to have been caused by operator or operating system error.
// *Action: Restore the log file from backup. If backup is not available,
// drop this log and re-create. If the database was shut down
// cleanly, no further action should be required; otherwise
// incomplete recovery may be required.
我们具体看一下logfile和controlfile的dump文件
控制文件:
DUMP OF CONTROL FILES, Seq # 9757 = 0x261d
V10 STYLE FILE HEADER:
Compatibility Vsn = 169870592=0xa200500
Db ID=4160719624=0xf7ff8b08, Db Name='ORA10G'
Activation ID=0=0x0
<span style="color:#ff0000;"><strong>Control Seq=9757=0x261d</strong></span>, File size=442=0x1ba
File Number=0, Blksiz=16384, File Type=1 CONTROL
*** END OF DUMP ***
*** 2014-09-27 03:19:18.924
DUMP OF CONTROL FILES, Seq # 9757 = 0x261d
V10 STYLE FILE HEADER:
Compatibility Vsn = 169870592=0xa200500
Db ID=4160719624=0xf7ff8b08, Db Name='ORA10G'
Activation ID=0=0x0
Control Seq=9757=0x261d, File size=442=0x1ba
File Number=0, Blksiz=16384, File Type=1 CONTROL
新库当前在线日志:
*** 2014-09-27 02:50:31.461
DUMP OF REDO FROM FILE '//u01/app/oracle/oradata/newdb/redo09.log.bak'
Opcodes *.*
RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
Times: creation thru eternity
FILE HEADER:
Compatibility Vsn = 169870592=0xa200500
Db ID=4160719624=0xf7ff8b08, Db Name='ORA10G'
Activation ID=4176815077=0xf8f523e5
<strong><span style="color:#ff0000;">Control Seq=9746=0x2612</span></strong>, File size=102400=0x19000
File Number=9, Blksiz=512, File Type=2 LOG
descrip:"Thread 0001, Seq# 0000000018, SCN 0x000000c8553f-0xffffffffffff"
thread: 1 nab: 0xffffffff seq: 0x00000012 hws: 0x2 eot: 1 dis: 0
resetlogs count: 0x33240a34 scn: 0x0000.00bcb9d5 (12368341)
resetlogs terminal rcv count: 0x0 scn: 0x0000.00000000
prev resetlogs count: 0x32c99947 scn: 0x0000.00bc1b73 (12327795)
prev resetlogs terminal rcv count: 0x0 scn: 0x0000.00000000
Low scn: 0x0000.00c8553f (13129023) 09/27/2014 02:28:46
旧库归档日志:
*** SESSION ID:(35.5) 2014-09-27 02:53:15.758
DUMP OF REDO FROM FILE '/u01/app/oracle/product/10.2.0/db_1/dbs/arch1_18_857999924.dbf'
Opcodes *.*
RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
Times: creation thru eternity
FILE HEADER:
Compatibility Vsn = 169870592=0xa200500
Db ID=4160719624=0xf7ff8b08, Db Name='ORA10G'
Activation ID=4176815077=0xf8f523e5
<span style="color:#ff0000;"><strong>Control Seq=9684=0x25d4<span style="color:#000000;">,</span></strong></span>File size=102400=0x19000
File Number=9, Blksiz=512, File Type=2 LOG
descrip:"Thread 0001, Seq# 0000000018, SCN 0x000000c80547-0x000000c80816"
thread: 1 nab: 0x4fa seq: 0x00000012 hws: 0x6 eot: 0 dis: 0
resetlogs count: 0x33240a34 scn: 0x0000.00bcb9d5 (12368341)
resetlogs terminal rcv count: 0x0 scn: 0x0000.00000000
prev resetlogs count: 0x32c99947 scn: 0x0000.00bc1b73 (12327795)
prev resetlogs terminal rcv count: 0x0 scn: 0x0000.00000000
Low scn: 0x0000.00c80547 (13108551) 09/27/2014 02:34:34
可见,旧库归档日志中的Control Seq,nab,Low scn,next scn都不同。原来,数据库每个物理文件头部都会加入很多"辨识符"来用于文件的检验。显示,旧库归档日志文件并不并新库控制文件所“授受”,因此不能用于新库的恢复,所以新库无法像DG一样应用归档日志实现数据同步!
-------------------------------------------------------------------------------------------------
本文来自于我的技术博客 http://blog.csdn.net/robo23
转载请标注源文链接,否则追究法律责任!