冷备数据库能否追溯应用旧库归档日志呢?通过实验来寻找答案!

最新推荐文章于 2017-10-19 14:46:41 发布

宋小荣

最新推荐文章于 2017-10-19 14:46:41 发布

阅读量847

点赞数

分类专栏：备份恢复文章标签：冷备数据库恢复同步应用归档日志

本文链接：https://blog.csdn.net/robo23/article/details/39601761

版权

备份恢复专栏收录该内容

10 篇文章 0 订阅

订阅专栏

问：冷备数据库能否追溯应用旧库归档日志呢？通过实验来寻找答案！

答案是不可以!下面我们通过做实验来证明：

先做数据库冷备并迁移至新目录，旧库保留:

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

转载请标注源文链接,否则追究法律责任!

宋小荣

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
冷备数据库能否追溯应用旧库归档日志呢?通过实验来寻找答案!

答案是不可以!下面我们通过做实验来证明先做数据库准备:SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 1
复制链接

扫一扫