ORACLE 11G通過SCN做增量備份修復standby庫詳細過程

orclwujian發表於2015-06-03

 

背景描述:Oracle 的standby庫後臺alert報錯,如下:


ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 10240change 11125950022 time 05/08/2015 22:00:41
ORA-00334: archived log:'/data/oracle/oradgdata/standby_archive/1_32350_821708334.dbf'
Recovery interrupted!
Wed May 13 13:26:08 2015
Trace dumping is performingid=[cdmp_20150513132608]
Wed May 13 13:26:08 2015
Sweep [inc][273026]: completed
Recovered data files to a consistent stateat change 11125946527
Errors in file/oracle/app/oracle/diag/rdbms/pddgunq/powerdes/trace/powerdes_pr00_21813.trc:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 10240change 11125950022 time 05/08/2015 22:00:41
ORA-00334: archived log:'/data/oracle/oradgdata/standby_archive/1_32350_821708334.dbf'
MRP0: Background Media Recovery processshutdown (powerdes)
Wed May 13 13:39:58 2015
Standby controlfile consistent with primary
RFS[3]: Selected log 5 for thread 1sequence 32488 dbid -903205653 branch 821708334
Wed May 13 13:39:58 2015
Archived Log entry 24243 added for thread 1sequence 32487 ID 0xca2ab4eb dest 3:


 

分析:

這表明standby已經無法應用歸檔了,在32350這個歸檔日誌錯誤,但是歸檔日誌檔案/data/oracle/oradgdata/standby_archive/1_32350_821708334.dbf存在,只是無法應用了。

 

以前使用Duplicate target database命令恢復線上oracle datagard備庫,但是這個是整個庫恢復,消耗時間比較長;想到oracle還有一種可以基於scn的方式來恢復standby庫,所以才有那個基於scn的增量備份來恢復standby庫。

 

1,去備庫查詢下未歸檔的記錄的起始值

SQL> select min(sequence#) fromv$archived_log where applied='NO'                                                                                                                             

 2  ;                                                                                                                                                                                        

 

MIN(SEQUENCE#)

--------------

          32350

 

SQL> select max(sequence#) fromv$archived_log where applied='NO';                                                                                                                            

 

MAX(SEQUENCE#)

--------------

          32508

 

SQL>

看到從32350到32508,都是沒有應用的歸檔日誌記錄。

 

 

 ----------------------------------------------------------------------------------------------------------------

原部落格地址:    http://blog.itpub.net/26230597/viewspace-1661116/
原作者:黃杉 (mchdba)
----------------------------------------------------------------------------------------------------------------

2,去主庫查詢增量備份需要的SCN號

去主庫查詢下傳輸過來的32530歸檔日誌所對應的scn號,查詢sql如下:

SELECT SEQUENCE#,to_char(FIRST_CHANGE#)fc,to_char(NEXT_CHANGE#)nc FROM v$archived_log WHERE SEQUENCE# > 32349 ORDERBY 1;

SELECT SEQUENCE#,to_char(FIRST_CHANGE#)fc,to_char(NEXT_CHANGE#)nc FROM v$archived_log WHERE SEQUENCE# = 32350 ORDER BY1;

SQL> SELECT SEQUENCE#,to_char(FIRST_CHANGE#)fc,to_char(NEXT_CHANGE#)nc FROM v$archived_log WHERE SEQUENCE# = 32350 ORDER BY1;                                                               

 

 SEQUENCE# FC                                                NC

-------------------------------------------------- ----------------------------------------

    32350 11125946510                                      11125975101

    32350 11125946510                                      11125975101

 

SQL>

我們看到主庫32350歸檔日誌在主庫對應的FIRST_CHANGE#scn號是11125946510

去主備庫檢查下scn,可以看到彼此scn不一致:

select to_char(current_scn) scn fromv$database;

主庫:

SQL> select to_char(current_scn) scnfrom v$database;                                                                                                                                         

 

SCN

----------------------------------------

11134239189

 

SQL>

備庫:

SQL> select to_char(current_scn) scnfrom v$database;                                                                                                                                         

 

SCN

----------------------------------------

11125946526

 

SQL>  

也可以按照備庫的最後一個scn號來在主庫上進行增量備份,不過為了保險起見,我們以主庫的scn號為準,那麼就需要基於11125946510這個scn號去主庫上做增量備份。

 

 

3,在主庫上設定log_archive_dest_state_2值,主庫的日誌無法歸檔到備庫.

先查詢自己的歸檔目錄select * from V$ARCHIVE_DEST;

 

PSselect * from V$ARCHIVE_DEST;查詢到STATUSVALID的,然後DESTINATIONPD_DG(這裡是備庫的標識)的dest編號就是往備庫傳輸歸檔日誌的;另外一個有目錄/oracle/app/oracle/flash_recovery_area/archivelog的就是主庫本身的歸檔日誌dest,這個目錄儲存的就是主庫本身的歸檔日誌

 

ALTER system SET log_archive_dest_state_2 ='defer';

SQL> ALTER system SETlog_archive_dest_state_2 = 'defer';                                                                                                                                     

                                                                                                                                                                                              

System altered.

 

SQL>

 

 

4,去備庫先停止備庫應用

ALTER DATABASE RECOVER MANAGED STANDBYDATABASE CANCEL;

SQL> ALTER DATABASE RECOVER MANAGEDSTANDBY DATABASE CANCEL;                                                                                                                                  

ALTER DATABASE RECOVER MANAGED STANDBYDATABASE CANCEL

*

ERROR at line 1:

ORA-16136: Managed Standby Recovery notactive

 

 

SQL> 

看到standby已經是not active了,所以不需要執行了。

5,在主庫執行備份

基於SCN增量備份:

backup device type disk incremental fromscn 11125946510 database format '/home/oracle/db_incre%U.bbk'; 

 

RMAN> backup device type diskincremental from scn 11125946510 database format'/home/oracle/db_incre%U.bbk';                                                                                 

 

Starting backup at 13-MAY-15                                                                                                                                                                   

 

using target database control file insteadof recovery catalog                                                                                                                                

allocated channel: ORA_DISK_1                                                                                                                                                                 

channel ORA_DISK_1: SID=212 devicetype=DISK

backup will be obsolete on date20-MAY-15                                                                                                                                                     

archived logs will not be kept or backed up

channel ORA_DISK_1: starting full datafilebackup set                                                                                                                                         

channel ORA_DISK_1: specifying datafile(s) inbackup set

input datafile file number=00005name=/home/oradata/powerdes/powerdesk01.dbf

input datafile file number=00003name=/home/oradata/powerdes/undotbs01.dbf

input datafile file number=00006name=/home/oradata/powerdes/plas01.dbf

input datafile file number=00001name=/home/oradata/powerdes/system01.dbf

input datafile file number=00002name=/home/oradata/powerdes/sysaux01.dbf

input datafile file number=00007name=/home/oradata/powerdes/pl01.dbf

input datafile file number=00011 name=/home/oradata/powerdes/plcrm01.dbf

input datafile file number=00004name=/home/oradata/powerdes/users01.dbf

input datafile file number=00008name=/home/oradata/powerdes/help01.dbf

input datafile file number=00009name=/home/oradata/powerdes/adobelc01.dbf

input datafile file number=00010name=/home/oradata/powerdes/sms01.dbf

channel ORA_DISK_1: starting piece 1 at13-MAY-15

channel ORA_DISK_1: finished piece 1 at13-MAY-15                                                                                                                                             

piecehandle=/home/oracle/db_increi3q6s13g_1_1.bbk tag=TAG20150513T202207comment=NONE

channel ORA_DISK_1: backup set complete,elapsed time: 00:09:45

                                                                                                                                                                                              

using channel ORA_DISK_1                                                                                                                                                                      

backup will be obsolete on date 20-MAY-15

archived logs will not be kept or backed up

channel ORA_DISK_1: starting full datafilebackup set                                                                                                                                         

channel ORA_DISK_1: specifying datafile(s)in backup set

including current control file in backupset                                                                                                                                                  

channel ORA_DISK_1: starting piece 1 at13-MAY-15

channel ORA_DISK_1: finished piece 1 at13-MAY-15                                                                                                                                             

piecehandle=/home/oracle/db_increi4q6s1lp_1_1.bbk tag=TAG20150513T202207comment=NONE

channel ORA_DISK_1: backup set complete,elapsed time: 00:00:01

Finished backup at 13-MAY-15                                                                                                                                                                  

                                                                                                                                                                                              

RMAN> 

 

檢視是否備份成功

RMAN> list backup of database;

......

BS Key Type LV Size       Device TypeElapsed Time Completion Time

------- ---- -- ---------- ----------------------- ---------------

3343   Incr    2.41G      DISK        00:09:35     13-MAY-15     

       BP Key: 3343   Status:AVAILABLE  Compressed: NO  Tag: TAG20150513T202207

       Piece Name: /home/oracle/db_increi3q6s13g_1_1.bbk

       Keep: NOLOGS             Until:20-MAY-15     

 List of Datafiles in backup set 3343

 File LV Type Ckp SCN    CkpTime  Name

 ---- -- ---- ---------- --------- ----

 1       Incr 11134253054 13-MAY-15/home/oradata/powerdes/system01.dbf

 2       Incr 11134253054 13-MAY-15/home/oradata/powerdes/sysaux01.dbf

 3       Incr 11134253054 13-MAY-15/home/oradata/powerdes/undotbs01.dbf

 4       Incr 11134253054 13-MAY-15/home/oradata/powerdes/users01.dbf

 5       Incr 11134253054 13-MAY-15/home/oradata/powerdes/powerdesk01.dbf

 6       Incr 11134253054 13-MAY-15/home/oradata/powerdes/plas01.dbf

 7       Incr 11134253054 13-MAY-15/home/oradata/powerdes/pl01.dbf

 8       Incr 11134253054 13-MAY-15/home/oradata/powerdes/help01.dbf

 9       Incr 11134253054 13-MAY-15/home/oradata/powerdes/adobelc01.dbf

 10      Incr 11134253054 13-MAY-15/home/oradata/powerdes/sms01.dbf

 11      Incr 11134253054 13-MAY-15/home/oradata/powerdes/plcrm01.dbf

                                                                                                                                                                                               

RMAN>

 

去檢視備份的檔案,看到2個bbk,表示備份成功,如下所示:

[oracle@localhost ~]$ ll db*

-rw-r----- 1 oracle oinstall 2591825920 May13 20:31 db_increi3q6s13g_1_1.bbk

-rw-r----- 1 oracle oinstall   19234816 May 13 20:31db_increi4q6s1lp_1_1.bbk

[oracle@localhost ~]$

 

6,在主庫重新生成控制檔案

ALTER DATABASE CREATE standby controlfileAS '/home/oracle/standby.ctl';

 

SQL> ALTER DATABASE CREATE standbycontrolfile AS '/home/oracle/standby.ctl';                                                                                                                 

                                                                                                                                                                                              

Database altered.

 

SQL>  

 

 

7,copy備份檔案到備庫

[oracle@localhost ~]$ scp db*/home/oracle/standby.ctl root@192.168.xx.xx:/data/oracle/backup/restore/

root@192.168.xx.xx's password:

db_increi3q6s13g_1_1.bbk                                                                                                                                    100% 2472MB  95.1MB/s   00:26   

db_increi4q6s1lp_1_1.bbk                                                                                                                                    100%   18MB  18.3MB/s  00:00   

standby.ctl                                                                                                                                                 100%   18MB 18.3MB/s   00:00   

[oracle@localhost ~]$

 

8,在備庫操作,準備好rman前環境

關閉備庫

SQL> shutdown immediate                                                                                                                                                                       

ORA-01109: database not open                                                                                                                                                                  

 

 

Database dismounted.                                                                                                                                                                          

ORACLE instance shut down.                                                                                                                                                                    

SQL>

 

然後啟動到nomont狀態

SQL> startup nomount                                                                                                                                                                           

ORACLE instance started.                                                                                                                                                                      

 

Total System Global Area 5344731136 bytes

Fixed Size              2213136 bytes

Variable Size              3489663728 bytes

Database Buffers    1811939328 bytes

Redo Buffers                40914944 bytes

SQL>

 

 

9,備庫上通過rman恢復控制檔案

 restore controlfile from'/data/oracle/backup/restore/standby.ctl';

RMAN> restore controlfile from'/data/oracle/backup/restore/standby.ctl';                                                                                                                     

 

Starting restore at 13-MAY-15

using channel ORA_DISK_1

 

RMAN-00571:===========================================================                                                                                                                       

RMAN-00569: =============== ERROR MESSAGESTACK FOLLOWS ===============

RMAN-00571:===========================================================

RMAN-03002: failure of restore command at05/13/2015 20:44:02

RMAN-06172: no AUTOBACKUP found orspecified handle is not a valid copy or piece

 

RMAN>

 

是目錄許可權的問題,需要設定成oracle使用者

[root@localhost restore]# ll

total 2568604

-rw-r----- 1 root root 2591825920 May 1320:35 db_increi3q6s13g_1_1.bbk

-rw-r----- 1 root root   19234816 May 13 20:35db_increi4q6s1lp_1_1.bbk

-rw-r----- 1 root root   19185664 May 13 20:35 standby.ctl

[root@localhost restore]# chown -Roracle.dba *

[root@localhost restore]#

 

RMAN> restore controlfile from'/data/oracle/backup/restore/standby.ctl';                                                                                                                     

 

Starting restore at 13-MAY-15

using target database control file insteadof recovery catalog

allocated channel: ORA_DISK_1                                                                                                                                                                 

channel ORA_DISK_1: SID=386 devicetype=DISK

                                                                                                                                                                                               

channel ORA_DISK_1: copied control filecopy                                                                                                                                                  

output filename=/home/oradata/powerdes/control01.ctl

output filename=/oracle/app/oracle/flash_recovery_area/powerdes/control02.ctl

Finished restore at 13-MAY-15

 

RMAN>

 

10,備庫上rman執行catalog操作

先將資料庫載入到mount,然後再執行catalog start with '/data/oracle/backup/restore'恢復

RMAN> alter database mount                                                                                                                                                                    

2> ;                                                                                                                                                                                          

 

using target database control file insteadof recovery catalog

database mounted                                                                                                                                                                              

                                                                                                                                                                                              

RMAN> catalog start with '/data/oracle/backup/restore'

RMAN> catalog start with'/data/oracle/backup/restore';                                                                                                                                       

 

Starting implicit crosscheck backup at13-MAY-15                                                                                                                                               

allocated channel: ORA_DISK_1                                                                                                                                                                 

channel ORA_DISK_1: SID=867 devicetype=DISK

Crosschecked 51 objects                                                                                                                                                                       

Finished implicit crosscheck backup at13-MAY-15

 

Starting implicit crosscheck copy at13-MAY-15

using channel ORA_DISK_1

Finished implicit crosscheck copy at13-MAY-15

 

searching for all files in the recoveryarea

cataloging files...

no files cataloged                                                                                                                                                                            

 

searching for all files that match thepattern /data/oracle/backup/restore

 

List of Files Unknown to the Database

=====================================

File Name:/data/oracle/backup/restore/db_increi3q6s13g_1_1.bbk

File Name:/data/oracle/backup/restore/db_increi4q6s1lp_1_1.bbk

File Name: /data/oracle/backup/restore/standby.ctl

 

Do you really want to catalog the abovefiles (enter YES or NO)? yes                                                                                                                          

cataloging files...

cataloging done                                                                                                                                                                               

 

List of Cataloged Files

=======================

File Name: /data/oracle/backup/restore/db_increi3q6s13g_1_1.bbk

File Name:/data/oracle/backup/restore/db_increi4q6s1lp_1_1.bbk

File Name:/data/oracle/backup/restore/standby.ctl

                                                                                                                                                                                              

RMAN>

 

 

 

11,備庫上rman執行restore操作

RECOVER DATABASE NOREDO; PS:加NOREDO不用線上日誌

RMAN> RECOVER DATABASE NOREDO;                                                                                                                                                                

 

Starting recover at 13-MAY-15                                                                                                                                                                 

using channel ORA_DISK_1

channel ORA_DISK_1: starting incrementaldatafile backup set restore                                                                                                                          

channel ORA_DISK_1: specifying datafile(s)to restore from backup set

destination for restore of datafile 00001:/home/oradata/powerdes/system01.dbf

destination for restore of datafile 00002:/home/oradata/powerdes/sysaux01.dbf

destination for restore of datafile 00003:/home/oradata/powerdes/undotbs01.dbf

destination for restore of datafile 00004:/home/oradata/powerdes/users01.dbf

destination for restore of datafile 00005:/home/oradata/powerdes/powerdesk01.dbf

destination for restore of datafile 00006:/home/oradata/powerdes/plas01.dbf

destination for restore of datafile 00007:/home/oradata/powerdes/pl01.dbf

destination for restore of datafile 00008:/home/oradata/powerdes/help01.dbf

destination for restore of datafile 00009:/home/oradata/powerdes/adobelc01.dbf

destination for restore of datafile 00010:/home/oradata/powerdes/sms01.dbf

destination for restore of datafile 00011:/home/oradata/powerdes/plcrm01.dbf

channel ORA_DISK_1: reading from backuppiece /data/oracle/backup/restore/db_increi3q6s13g_1_1.bbk

channel ORA_DISK_1: piecehandle=/data/oracle/backup/restore/db_increi3q6s13g_1_1.bbktag=TAG20150513T202207                                                                                  

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete,elapsed time: 00:05:45

                                                                                                                                                                                              

Finished recover at 13-MAY-15                                                                                                                                                                 

                                                                                                                                                                                              

RMAN> 

 

後臺alert日誌輸出為:

Using STANDBY_ARCHIVE_DEST parameterdefault value as /data/oracle/oradgdata/standby_archive

Wed May 13 20:54:42 2015

Incremental restore complete of datafile 4/home/oradata/powerdes/users01.dbf

 checkpoint is 11134253054

 last deallocation scn is 10905979642

Wed May 13 20:54:53 2015

Incremental restore complete of datafile 8/home/oradata/powerdes/help01.dbf

 checkpoint is 11134253054

 last deallocation scn is 9881798870

Wed May 13 20:55:08 2015

Incremental restore complete of datafile 9/home/oradata/powerdes/adobelc01.dbf

 checkpoint is 11134253054

Wed May 13 20:55:21 2015

Incremental restore complete of datafile 10/home/oradata/powerdes/sms01.dbf

 checkpoint is 11134253054

Wed May 13 20:55:34 2015

Incremental restore complete of datafile 11/home/oradata/powerdes/plcrm01.dbf

 checkpoint is 11134253054

Incremental restore complete of datafile 7/home/oradata/powerdes/pl01.dbf

 checkpoint is 11134253054

 last deallocation scn is 10905470965

Wed May 13 20:56:24 2015

db_recovery_file_dest_size of 15360 MB is0.00% used. This is a

user-specified limit on the amount of spacethat will be used by this

database for recovery-related files, anddoes not reflect the amount of

space available in the underlyingfilesystem or ASM diskgroup.

Wed May 13 20:56:26 2015

Incremental restore complete of datafile 2/home/oradata/powerdes/sysaux01.dbf

 checkpoint is 11134253054

 last deallocation scn is 10906515871

Wed May 13 20:56:43 2015

Incremental restore complete of datafile 1/home/oradata/powerdes/system01.dbf

 checkpoint is 11134253054

  last deallocation scn is 10825889348

Incremental restore complete of datafile 6/home/oradata/powerdes/plas01.dbf

 checkpoint is 11134253054

 last deallocation scn is 10905977986

Incremental restore complete of datafile 3/home/oradata/powerdes/undotbs01.dbf

 checkpoint is 11134253054

 last deallocation scn is 10908635608

Wed May 13 20:59:58 2015

Incremental restore complete of datafile 5/home/oradata/powerdes/powerdesk01.dbf

 checkpoint is 11134253054

 last deallocation scn is 10906663694

 

12,備庫上重新啟動應用日誌

 ALTER DATABASE recover managed standby DATABASE disconnect FROM SESSION;

         SQL>ALTER DATABASE recover managed standby DATABASE disconnect FROM SESSION;                                                                                                                 

                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

         Databasealtered.

 

         SQL>  

 

  後臺alert日誌輸出為:

 ALTER DATABASE recover managed standby DATABASE disconnect FROM SESSION

         Attemptto start background Managed Standby Recovery process (powerdes)

         WedMay 13 21:04:41 2015

         MRP0started with pid=19, OS id=23729

         MRP0:Background Managed Standby Recovery process started (powerdes)

          started logmerger process

         WedMay 13 21:04:46 2015

         ManagedStandby Recovery not using Real Time Apply

         ParallelMedia Recovery started with 16 slaves

         Waitingfor all non-current ORLs to be archived...

         Allnon-current ORLs have been archived.

         MediaRecovery Waiting for thread 1 sequence 32510

         Completed:ALTER DATABASE recover managed standby DATABASE disconnect FROM SESSION

 

13,主庫操作往備庫傳輸歸檔日誌

命令為:

ALTER system SET log_archive_dest_state_2 ='enable';

 

去備庫後臺alert日誌資訊為:

Standby controlfile consistent with primary

SRL log 4 needs clearing because log hasnot been created

Errors in file/oracle/app/oracle/diag/rdbms/pddgunq/powerdes/trace/powerdes_rfs_23767.trc:

ORA-00367: checksum error in log fileheader

ORA-00315: log 4 of thread 0, wrong thread# 1 in header

ORA-00312: online log 4 thread 0:'/home/oradata/powerdes/redo_dg_01.log'

SRL log 5 needs clearing because log hasnot been created

Errors in file/oracle/app/oracle/diag/rdbms/pddgunq/powerdes/trace/powerdes_rfs_23767.trc:

ORA-00367: checksum error in log fileheader

ORA-00315: log 5 of thread 0, wrong thread# 1 in header

ORA-00312: online log 5 thread 0:'/home/oradata/powerdes/redo_dg_02.log'

SRL log 6 needs clearing because log hasnot been created

Errors in file/oracle/app/oracle/diag/rdbms/pddgunq/powerdes/trace/powerdes_rfs_23767.trc:

ORA-00367: checksum error in log fileheader

ORA-00315: log 6 of thread 0, wrong thread# 1 in header

ORA-00312: online log 6 thread 0:'/home/oradata/powerdes/redo_dg_03.log'

SRL log 5 needs clearing because log hasnot been created

Errors in file/oracle/app/oracle/diag/rdbms/pddgunq/powerdes/trace/powerdes_rfs_23767.trc:

ORA-00367: checksum error in log fileheader

ORA-00315: log 5 of thread 0, wrong thread# 1 in header

ORA-00312: online log 5 thread 0:'/home/oradata/powerdes/redo_dg_02.log'

SRL log 6 needs clearing because log hasnot been created

Errors in file/oracle/app/oracle/diag/rdbms/pddgunq/powerdes/trace/powerdes_rfs_23767.trc:

ORA-00367: checksum error in log fileheader

ORA-00315: log 6 of thread 0, wrong thread# 1 in header

ORA-00312: online log 6 thread 0: '/home/oradata/powerdes/redo_dg_03.log'

SRL log 6 needs clearing because log hasnot been created

Errors in file/oracle/app/oracle/diag/rdbms/pddgunq/powerdes/trace/powerdes_rfs_23767.trc:

ORA-00367: checksum error in log fileheader

ORA-00315: log 6 of thread 0, wrong thread# 1 in header

ORA-00312: online log 6 thread 0:'/home/oradata/powerdes/redo_dg_03.log'

RFS[1]: No standby redo logfiles of filesize 52428800 AND block size 512 exist

Clearing online log 6 of thread 0 sequencenumber 0

Errors in file/oracle/app/oracle/diag/rdbms/pddgunq/powerdes/trace/powerdes_rfs_23767.trc:

ORA-00367: checksum error in log fileheader

ORA-00315: log 6 of thread 0, wrong thread# 1 in header

ORA-00312: online log 6 thread 0:'/home/oradata/powerdes/redo_dg_03.log'

Wed May 13 21:06:47 2015

Clearing online log 4 of thread 0 sequencenumber 0

Errors in file/oracle/app/oracle/diag/rdbms/pddgunq/powerdes/trace/powerdes_arc4_23630.trc:

ORA-00367: checksum error in log fileheader

ORA-00315: log 4 of thread 0, wrong thread# 1 in header

ORA-00312: online log 4 thread 0:'/home/oradata/powerdes/redo_dg_01.log'

Clearing online log 5 of thread 0 sequencenumber 0

Errors in file/oracle/app/oracle/diag/rdbms/pddgunq/powerdes/trace/powerdes_arc4_23630.trc:

ORA-00367: checksum error in log fileheader

ORA-00315: log 5 of thread 0, wrong thread# 1 in header

ORA-00312: online log 5 thread 0:'/home/oradata/powerdes/redo_dg_02.log'

RFS[1]: Selected log 4 for thread 1sequence 32511 dbid -903205653 branch 821708334

Wed May 13 21:06:51 2015

RFS[2]: Assigned to RFS process 23775

RFS[2]: Identified database type as'physical standby': Client is ARCH pid 20139

RFS[2]: Selected log 5 for thread 1sequence 32510 dbid -903205653 branch 821708334

Wed May 13 21:06:52 2015

RFS[3]: Assigned to RFS process 23777

RFS[3]: Identified database type as'physical standby': Client is ARCH pid 20131

Wed May 13 21:06:52 2015

Archived Log entry 1 added for thread 1sequence 32510 ID 0xca2ab4eb dest 3:

Wed May 13 21:06:52 2015

Media Recovery Log/data/oracle/oradgdata/standby_archive/1_32510_821708334.dbf

Wed May 13 21:06:54 2015

Archived Log entry 2 added for thread 1sequence 32511 ID 0xca2ab4eb dest 3:

Changing standby controlfile to MAXIMUMAVAILABILITY level

RFS[1]: Selected log 4 for thread 1sequence 32512 dbid -903205653 branch 821708334

Wed May 13 21:07:07 2015

Media Recovery Log/data/oracle/oradgdata/standby_archive/1_32511_821708334.dbf

Media Recovery Waiting for thread 1sequence 32512 (in transit)

 

 

14,去主庫備庫檢視歸檔日誌情況,是否保持一致

主庫:

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     32510

Next log sequence to archive   32512

Current log sequence             32512

SQL> 

 

備庫:

SQL> archive log list;                                                                                                                                                                         

Database log mode        Archive Mode

Automatic archival         Enabled

Archive destination        /data/oracle/oradgdata/standby_archive

Oldest online log sequence     32511

Next log sequence to archive   0

Current log sequence             32512

SQL> 

 

15,在主庫切下歸檔日誌,看是否應用到備庫

PS:此操作有一定危險性,切勿在業務高峰期操作,最後在凌晨時分業務低峰時期操作,以免誤傷無數,^_^

alter system switch logfile;

SQL> alter system switch logfile;                                                                                                                                                             

                                                                                                                                                                                              

System altered.

 

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     32511

Next log sequence to archive   32513

Current log sequence             32513

SQL> 

 

16,在主庫切完日誌後,去備庫看下日誌有沒有傳輸過去並應用起來

備庫alert日誌

Wed May 13 21:11:23 2015

Media Recovery Log/data/oracle/oradgdata/standby_archive/1_32512_821708334.dbf

Media Recovery Waiting for thread 1sequence 32513 (in transit)

 

備庫alert loglist;

SQL> archive log list;                                                                                                                                                                        

Database log mode        Archive Mode

Automatic archival         Enabled

Archive destination       /data/oracle/oradgdata/standby_archive

Oldest online log sequence     32512

Next log sequence to archive   0

Current log sequence             32513

SQL>

 

17,以open read only模式開啟備庫,在備庫操作

先檢視資料庫狀態

SQL> select open_mode fromv$database;                                                                                                                                                        

 

OPEN_MODE

--------------------

MOUNTED

 

SQL>

 

取消applied

alter database recover managed standbydatabase using current logfile disconnect from session;

SQL> alter database recover managedstandby database using current logfile disconnect from session;                                                                                          

                                     

alter database recover managed standbydatabase using current logfile disconnect from session                                                                                                 

*

ERROR at line 1:

ORA-01153: an incompatible media recoveryis active

 

SQL> SQL> 

 

有報錯,如下解決

SQL> alter database recover managedstandby database cancel;                                                                                                                                  

                                                                                                                                                                                              

Database altered.

 

SQL> 

 

再檢視下是否有未應用的NO的,沒有NO的,正常如下:

SQL> select sequence# ,applied fromv$archived_log where applied='NO' order by sequence# ;                                                                                                    

no rows selected

SQL> 

 

以open readonly模式開啟

SQL> alter database open read only;

Database altered.

SQL>

SQL> select open_mode fromv$database;                                                                                                                                                      

OPEN_MODE

--------------------

READ ONLY

 

SQL>

 

然後啟動應用

SQL> alter database recover managedstandby database disconnect from session;                                                                                                                 

                                                                                                                                                                                              

Database altered.

 

SQL> 

SQL> select open_mode fromv$database;                                                                                                                                                        

OPEN_MODE

--------------------

READ ONLY WITH APPLY

 

SQL>

 

 

18,再檢查主庫備庫是否一致

通過以下命令檢視:

select sequence#,applied fromv$archived_log order by sequence# asc;

archive log list;

 

查詢最大歸檔日誌序列號:

SELECTSEQUENCE#,to_char(FIRST_CHANGE#),to_char(NEXT_CHANGE#) FROM v$archived_logWHERE SEQUENCE# > 32507 ORDER BY 1;

 

 

參考文章:http://www.cnblogs.com/shawnloong/p/3970419.html

 

 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29989552/viewspace-1683547/,如需轉載,請註明出處,否則將追究法律責任。

相關文章