Duplicate複製資料庫並創建物理StandBy(spfile+不同實例名+不同路徑)

過程和Duplicate複製資料庫並創建物理StandBy類似，只是不需要重啟資料庫。目的：創建standby，不重啟源資料庫 1設定環境如下： Primary資料庫 IP 172.17.22.16 SID orcl Standby資料庫 IP 172.17.22.17 SID orcl_stan ...

過程和Duplicate複製資料庫並創建物理StandBy類似，只是不需要重啟資料庫。

目的：創建standby，不重啟源資料庫

1設定環境如下：

Primary資料庫

IP	172.17.22.16
SID	orcl

Standby資料庫

IP	172.17.22.17
SID	orcl_standby

設置提示，以區分操作的位置

primary資料庫

set SQLPROMPT Primary>

standby資料庫

set SQLPROMPT StandBy>

1、Primary端設置歸檔模式

確保primary資料庫運行在歸檔模式

Primary>archive log list
Database log mode           No Archive Mode
Automatic archival           Disabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     5
Current log sequence           7
Primary>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Primary>startup mount
ORACLE instance started.

Total System Global Area 3290345472 bytes
Fixed Size            2217832 bytes
Variable Size         1795164312 bytes
Database Buffers     1476395008 bytes
Redo Buffers           16568320 bytes
Database mounted.
Primary>alter database archivelog;

Database altered.

Primary>alter database open;

Database altered.


Primary>archive log list
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     5
Next log sequence to archive   7
Current log sequence           7
Primary>

2、Primary端開啟強制日誌

Primary>select force_logging from v$database;

FOR
---
NO

Primary>alter database force logging;

Database altered.

關於FORCE LOGGING

想必大家知道有一些DDL語句可以通過指定NOLOGGING子句的方式避免寫redo log(目的是提高速度，某些時候確實有效)，指定資料庫為FORCE LOGGING模式後，資料庫將會記錄除臨時表空間或臨時回滾段外所有的操作而忽略類似NOLOGGING之類的指定參數。如果在執行force logging時有nologging之類的語句在執行，則force logging會等待直到這類語句全部執行。FORCE LOGGING是做為固定參數保存在控制文件中，因此其不受重啟之類操作的影響(只執行一次即可)，如果想取消，可以通過alter database no force logging語句關閉強制記錄。

3、standby端創建相關目錄

為了和Primary庫保存相同的結構，我們需要在Standby資料庫建立相同的目錄，首先查詢現有Primary資料庫的相關目錄

Primary>col name for a30
Primary>col value for a100
Primary>select name ,value from v$parameter  where name in ('audit_file_dest','background_dump_dest','control_files','core_dump_dest','user_dump_dest') ORDER BY name ASC;

NAME                   VALUE
------------------------------ ---------------------------------------------------------------------------------------------
audit_file_dest            /usr/oracle/app/admin/orcl/adump
background_dump_dest    /usr/oracle/app/diag/rdbms/orcl/orcl/trace
control_files           /usr/oracle/app/oradata/orcl/control01.ctl, /usr/oracle/app/flash_recovery_area/orcl/control02.ctl
core_dump_dest          /usr/oracle/app/diag/rdbms/orcl/orcl/cdump
user_dump_dest          /usr/oracle/app/diag/rdbms/orcl/orcl/trace

在standby資料庫伺服器創建如下的目錄：

[oracle@oracledb ~]$ mkdir -p /usr/oracle/standby/app/admin/orcl_standby/adump
[oracle@oracledb ~]$ mkdir -p /usr/oracle/standby/app/diag/rdbms/orcl_standby/orcl_standby/trace
[oracle@oracledb ~]$ mkdir -p /usr/oracle/standby/app/oradata/orcl_standby
[oracle@oracledb ~]$ mkdir -p /usr/oracle/standby/app/flash_recovery_area/orcl_standby
[oracle@oracledb ~]$ mkdir -p /usr/oracle/standby/app/diag/rdbms/orcl_standby/orcl_standby/

3、創建輔助實例密鑰文件

方法一：直接從Primary資料庫複製密鑰文件過來 (如果ORACLE_SID不同需要改名)

[oracle@oracledb dbs]$ scp orapworcl 172.17.22.17:/usr/oracle/standby/app/product/11.2.0/dbhome_1/dbs
[email protected]'s password: 
orapworcl                                     100% 1536     1.5KB/s   00:00    
[oracle@oracledb dbs]$

改名

[oracle@oracledb dbs]$ mv orapworcl  orapworcl_standby

方法二： orapwd生成

orapwd FILE=/usr/oracle/standby/app/product/11.2.0/dbhome_1/dbs/orapworcl password=wangshengzhuang entries=30;

4、修改primary端初始化參數,

需要配置如下的參數,參數說明如下

1、與主庫角色相關的初始化參數說明：
DB_NAME

註意保持同一個DataGuard環境中所有資料庫DB_NAME相同

DB_UNIQUE_NAME

為每一個資料庫指定一個唯一的名稱,以標示同一個dataguard環境中不同的資料庫。

LOG_ARCHIVE_CONFIG

該參數通過DG_CONFIG屬性羅列同一個Data Guard中所有DB_UNIQUE_NAME(含主庫db及備庫db)，以逗號分隔。

例如：LOG_ARCHIVE_CONFIG='DB_CONFIG=(orcl,orcl2)'

LOG_ARCHIVE_DEST_n

歸檔文件的生成路徑。該參數非常重要,dataguard就是通過這裡的設置傳輸日誌的。

LOG_ARCHIVE_DEST_STATE_n

指定參數值為ENABLE，標示對應的LOG_ARCHIVE_DEST_n參數是否有效。

REMOTE_LOGIN_PASSWORDFILE

推薦設置參數值為EXCLUSIVE或者SHARED，註意保證相同Data Guard配置中所有db伺服器sys密碼相同。如果不同日誌傳輸會失敗。資料庫預設是EXCLUSIVE,一般不用修改。

LOG_ARCHIVE_FORMAT

指定歸檔文件格式。一般也不用修改，保持預設即可

2、以下參數為備庫角色相關的參數，建議在主庫的初始化參數中也進行設置，這樣在主備庫角色相互轉換後不需要做修改dataguard也能正常運行。

FAL_SERVER

指定備庫到主資料庫的連接服務名，FAL_SERVER = orcl2日誌所在伺服器。

FAL_CLIENT

指定主庫到備庫的連接服務名，FAL_CLIENT = orcl日誌接收客戶端。

STANDBY_FILE_MANAGEMENT

如果主庫的數據文件發生修改（如新建，重命名等）則按照本參數的設置在備庫中做相應修改。設為AUTO表示自動管理。設為MANUAL表示需要手工管理。

例如：STANDBY_FILE_MANAGEMENT=AUTO

在Primary端根據spfile生成pfile，並備份

Primary>create pfile from spfile;

File created.

查詢Primary庫的db_unique_name，（預設和db_name相同）

Primary>show parameter db_unique_name;

NAME                     TYPE            VALUE
------------------------------------ ---------------------- ------------------------------
db_unique_name                 string            orcl

修改spfile參數值

-------為了不重啟,沿用上面的db_unique_name   
--alter system set DB_UNIQUE_NAME=orcl scope=spfile


----這裡的orcl和orcl_standby為別為主庫和備庫的db_unique_name
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcl_standby)' scope=spfile   


-----這裡的service為主庫連接到備庫的服務名，後面會在tnsnames.ora文件中配置 valid_for參數說明這個歸檔日誌目的地在本資料庫為主庫的角色下才需要把online_logfile傳輸到備庫去。arch async NOAFFIRM說明的是同步的方式，這個同步的方式有三種方式，最大保護，最大性能，最大可用。每個方式有不同的設置，具體見後面說明
alter system set LOG_ARCHIVE_DEST_2='SERVICE=tns_standby ARCH ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl_standby'
----- 暫時不啟用日誌傳送
alter system set LOG_ARCHIVE_DEST_STATE_2=DEFER


------以下修改的是作為備庫角色需要的參數，為了方便以後主備庫切換，建議在主庫中也配置作為備庫角色的相關參數。
alter system set FAL_SERVER=tns_standby
alter system set FAL_CLIENT=tns_primary
alter system set STANDBY_FILE_MANAGEMENT=AUTOalter system set  DB_FILE_NAME_CONVERT='/usr/oracle/standby/app/oradata/orcl_standby/','/usr/oracle/app/oradata/orcl/' scope=spfile;alter system set LOG_FILE_NAME_CONVERT='/usr/oracle/standby/app/oradata/orcl_standby/','/usr/oracle/app/oradata/orcl/' scope=spfile;

生成standby端的pfile

在Primary端根據spfile重新生成pfile，用以啟動standby資料庫

Primary>create pfile from spfile;

File created.

複製生成的initorcl.ora到standby的$ORACLE_HOME/dbs目錄下，並改名(因為實例名不同)

[oracle@oracledb dbs]$ pwd
/usr/oracle/app/product/11.2.0/dbhome_1/dbs
[oracle@oracledb dbs]$ scp initorcl.ora 172.17.22.17:/usr/oracle/standby/app/product/11.2.0/dbhome_1/dbs
The authenticity of host '172.17.22.17 (172.17.22.17)' can't be established.
RSA key fingerprint is 72:28:f5:f9:9c:f8:49:23:48:6d:9d:d4:0e:0c:89:71.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '172.17.22.17' (RSA) to the list of known hosts.
[email protected]'s password: 
initorcl.ora                                  100% 1291     1.3KB/s   00:00    
[oracle@oracledb dbs]$

改名

[oracle@oracledb dbs]$ mv initorcl.ora  initorcl_standby.ora

修改standbyd端的initorcl_standby.ora文件，內容如下

------這裡的實例名要由orcl改為orcl_standbyorcl_standby.__db_cache_size=1476395008
orcl_standby.__java_pool_size=16777216
orcl_standby.__large_pool_size=16777216
orcl_standby.__oracle_base='/usr/oracle/app'#ORACLE_BASE set from environment
orcl_standby.__pga_aggregate_target=1325400064
orcl_standby.__sga_target=1979711488
orcl_standby.__shared_io_pool_size=0
orcl_standby.__shared_pool_size=436207616
orcl_standby.__streams_pool_size=0
*.audit_file_dest='/usr/oracle/standby/app/admin/orcl_standby/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/usr/oracle/standby/app/oradata/orcl_standby/control01.ctl','/usr/oracle/standby/app/flash_recovery_area/orcl_standby/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/usr/oracle/standby/app/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/usr/oracle/standby/app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.memory_target=3299868672
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'




*.DB_UNIQUE_NAME=orcl_standby
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcl_standby)'
*.LOG_ARCHIVE_DEST_2='SERVICE=tns_primary ARCH ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'  #arch表示歸檔日誌
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE

------路徑轉換
   *.DB_FILE_NAME_CONVERT= '/usr/oracle/app/oradata/orcl/','/usr/oracle/standby/app/oradata/orcl_standby/'    *.LOG_FILE_NAME_CONVERT='/usr/oracle/app/oradata/orcl/','/usr/oracle/standby/app/oradata/orcl_standby/' *.FAL_SERVER=tns_primary *.FAL_CLIENT=tns_standby*.STANDBY_FILE_MANAGEMENT=AUTO

通過複製的pfile創建Standby資料庫的spfile

StandBy> create spfile from pfile;

File created.

啟動到nomount環境

SQL> startup nomount
ORACLE instance started.

Total System Global Area 3290345472 bytes
Fixed Size            2217832 bytes
Variable Size         1795164312 bytes
Database Buffers     1476395008 bytes
Redo Buffers           16568320 bytes

5、配置監聽服務

Primary端監聽（應該已經存在，大多數情況下不用重新配置）

# listener.ora Network Configuration File: /usr/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.22.16)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /usr/oracle/app

StandBy端監聽（配置了靜態監聽服務名GLOBAL_DBNAME = StandBy）

# listener.ora Network Configuration File: /usr/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.



SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = StandBy)
      (ORACLE_HOME =/usr/oracle/standby/app/product/11.2.0/dbhome_1)
      (SID_NAME = orcl_standby)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.22.17)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /usr/oracle/app

啟動standby端監聽

[oracle@oracledb admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 23-DEC-2015 15:48:15

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Starting /usr/oracle/standby/app/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /usr/oracle/standby/app/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /usr/oracle/standby/app/diag/tnslsnr/oracledb/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.17.22.17)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                23-DEC-2015 15:48:16
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /usr/oracle/standby/app/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /usr/oracle/standby/app/diag/tnslsnr/oracledb/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.17.22.17)(PORT=1521)))
Services Summary...
Service "StandBy" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

6、配置網路服務名，並測試互通性

Primary端和StandBy端都要進行如下配置：

tns_primary =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.22.16)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )


tns_standby =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.22.17)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME =StandBy )
    )
  )

***********************************************************************************************

SERVICE_NAME 的值參考lsnrctl的輸出，確保用sqlplus工具或者navicact工具能夠連接SERVICE_NAME

因為fal_server 會使用服務名，如果配置不正確，這歸檔日誌無法正常發送

***********************************************************************************************

在primary端和standby端都進行測試（為了角色切換）

[oracle@oracledb admin]$ tnsping tns_primary

[oracle@oracledb admin]$ tnsping tns_standby

7、duplicate standby

rman連接兩個資料庫

[oracle@oracledb admin]$ rman target sys/wangshengzhuang@tns_primary auxiliary sys/wangshengzhuang@tns_standby

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Dec 23 16:11:51 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1426832466)
connected to auxiliary database: ORCL (not mounted)

開始複製

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;

Starting Duplicate Db at 23-DEC-15
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/23/2015 16:13:38
RMAN-05541: no archived logs found in target database

primary端切換下日誌，quit重新執行rman duplicate即可

[oracle@oracledb ~]$ rman target sys/wangshengzhuang@tns_primary auxiliary sys/wangshengzhuang@tns_standby 

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Dec 30 16:56:13 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1426832466)
connected to auxiliary database: ORCL (not mounted)

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;

Starting Duplicate Db at 30-DEC-15
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/usr/oracle/app/product/11.2.0/dbhome_1/dbs/orapworcl' auxiliary format 
 '/usr/oracle/standby/app/product/11.2.0/dbhome_1/dbs/orapworcl_standby'   ;
}
executing Memory Script

Starting backup at 30-DEC-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK
Finished backup at 30-DEC-15

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/usr/oracle/standby/app/oradata/orcl_standby/control01.ctl';
   restore clone controlfile to  '/usr/oracle/standby/app/flash_recovery_area/orcl_standby/control02.ctl' from 
 '/usr/oracle/standby/app/oradata/orcl_standby/control01.ctl';
}
executing Memory Script

Starting backup at 30-DEC-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/usr/oracle/app/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f tag=TAG20151230T165618 RECID=4 STAMP=899830578
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 30-DEC-15

Starting restore at 30-DEC-15
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 30-DEC-15

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to 
 "/usr/oracle/standby/app/oradata/orcl_standby/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to 
 "/usr/oracle/standby/app/oradata/orcl_standby/system01.dbf";
   set newname for datafile  2 to 
 "/usr/oracle/standby/app/oradata/orcl_standby/sysaux01.dbf";
   set newname for datafile  3 to 
 "/usr/oracle/standby/app/oradata/orcl_standby/undotbs01.dbf";
   set newname for datafile  4 to 
 "/usr/oracle/standby/app/oradata/orcl_standby/users01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format 
 "/usr/oracle/standby/app/oradata/orcl_standby/system01.dbf"   datafile 
auxiliary format 
 "/usr/oracle/standby/app/oradata/orcl_standby/sysaux01.dbf"   datafile 
auxiliary format 
 "/usr/oracle/standby/app/oradata/orcl_standby/undotbs01.dbf"   datafile 
auxiliary format 
 "/usr/oracle/standby/app/oradata/orcl_standby/users01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /usr/oracle/standby/app/oradata/orcl_standby/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 30-DEC-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/usr/oracle/app/oradata/orcl/system01.dbf
output file name=/usr/oracle/standby/app/oradata/orcl_standby/system01.dbf tag=TAG20151230T165625
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/usr/oracle/app/oradata/orcl/sysaux01.dbf
output file name=/usr/oracle/standby/app/oradata/orcl_standby/sysaux01.dbf tag=TAG20151230T165625
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:16
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/usr/oracle/app/oradata/orcl/undotbs01.dbf
output file name=/usr/oracle/standby/app/oradata/orcl_standby/undotbs01.dbf tag=TAG20151230T165625
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/usr/oracle/app/oradata/orcl/users01.dbf
output file name=/usr/oracle/standby/app/oradata/orcl_standby/users01.dbf tag=TAG20151230T165625
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 30-DEC-15

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "/usr/oracle/app/flash_recovery_area/ORCL/archivelog/2015_12_30/o1_mf_1_8_c8772wkh_.arc" auxiliary format 
 "/usr/oracle/standby/app/flash_recovery_area/ORCL_STANDBY/archivelog/2015_12_30/o1_mf_1_8_%u_.arc"   ;
   catalog clone recovery area;
   switch clone datafile all;
}
executing Memory Script

Starting backup at 30-DEC-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=8 RECID=2 STAMP=899830620
output file name=/usr/oracle/standby/app/flash_recovery_area/ORCL_STANDBY/archivelog/2015_12_30/o1_mf_1_8_0lqq4kqs_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 30-DEC-15

searching for all files in the recovery area

List of Files Unknown to the Database
=====================================
File Name: /usr/oracle/standby/app/flash_recovery_area/ORCL_STANDBY/archivelog/2015_12_30/o1_mf_1_8_0lqq4kqs_.arc
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /usr/oracle/standby/app/flash_recovery_area/ORCL_STANDBY/archivelog/2015_12_30/o1_mf_1_8_0lqq4kqs_.arc

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=899830621 file name=/usr/oracle/standby/app/oradata/orcl_standby/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=5 STAMP=899830621 file name=/usr/oracle/standby/app/oradata/orcl_standby/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=899830621 file name=/usr/oracle/standby/app/oradata/orcl_standby/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=899830621 file name=/usr/oracle/standby/app/oradata/orcl_standby/users01.dbf

contents of Memory Script:
{
   set until scn  1027736;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 30-DEC-15
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 8 is already on disk as file /usr/oracle/standby/app/flash_recovery_area/ORCL_STANDBY/archivelog/2015_12_30/o1_mf_1_8_0lqq4kqs_.arc
archived log file name=/usr/oracle/standby/app/flash_recovery_area/ORCL_STANDBY/archivelog/2015_12_30/o1_mf_1_8_0lqq4kqs_.arc thread=1 sequence=8
media recovery complete, elapsed time: 00:00:00
Finished recover at 30-DEC-15
Finished Duplicate Db at 30-DEC-15

8、啟用日誌傳送

Primary>show parameter LOG_ARCHIVE_DEST_STATE_2

NAME                     TYPE            VALUE
------------------------------------ ---------------------- ------------------------------
log_archive_dest_state_2         string            DEFER
log_archive_dest_state_20         string            enable
log_archive_dest_state_21         string            enable
log_archive_dest_state_22         string            enable
log_archive_dest_state_23         string            enable
log_archive_dest_state_24         string            enable
log_archive_dest_state_25         string            enable
log_archive_dest_state_26         string            enable
log_archive_dest_state_27         string            enable
log_archive_dest_state_28         string            enable
log_archive_dest_state_29         string            enable
Primary>alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;

System altered.

8、驗證結果

查詢primary資料庫角色

Primary>select database_role from v$database;

DATABASE_ROLE
--------------------------------
PRIMARY

查詢standby資料庫角色

StandBy>select database_role from v$database;

DATABASE_ROLE
--------------------------------
PHYSICAL STANDBY

StandBy>

primary端插入一條數據

Primary> INSERT INTO "SCOTT"."DEPT" ("DEPTNO", "DNAME", "LOC") VALUES ('12', 'OPERATIONS', 'OPERATIONS');
row created.

Primary>commit;

Commit complete.

Primary>alter system switch logfile;

System altered.

standby端：啟動redo應用、暫停redo應用打開資料庫，查詢數據是否被同步過來了

StandBy>alter database recover managed standby database disconnect from session;

Database altered.

StandBy>alter database recover managed standby database cancel;

Database altered.

StandBy>alter database open;

Database altered.

StandBy>select open_mode from v$database;

OPEN_MODE
----------------------------------------
READ ONLY

StandBy>

見證奇跡的時刻：

StandBy>select * from scott.dept;

    DEPTNO DNAME            LOC
---------- ---------------------------- --------------------------
ACCOUNTING            NEW YORK
RESEARCH            DALLAS
SALES            CHICAGO
OPERATIONS            BOSTON
OPERATIONS            OPERATIONS

9、啟用實時應用redo

添加redo log

首先查詢當前redo log的大小、位置

Primary>col group# for 9
Primary>col status for a10
Primary>col type for a10
Primary>col member for a50;
Primary>col is_rec for a10
Primary>select * from v$logfile;

GROUP# STATUS      TYPE         MEMBER                        IS_REC
------ ---------- ---------- -------------------------------------------------- ------
         ONLINE     /usr/oracle/app/oradata/orcl/redo03.log        NO
         ONLINE     /usr/oracle/app/oradata/orcl/redo02.log        NO
         ONLINE     /usr/oracle/app/oradata/orcl/redo01.log        NO

Primary>select * from v$log;

GROUP#      THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARCHIV STATUS    FIRST_CHANGE# FIRST_TIME   NEXT_CHANGE# NEXT_TIME
------ ---------- ---------- ---------- ---------- ---------- ------ ---------- ------------- ------------ ------------ ------------
       1      10   52428800        512        1 NO     CURRENT          1029234 23-DEC-15      2.8147E+14
       1       8   52428800        512        1 YES    INACTIVE          1028340 23-DEC-15     1028421 23-DEC-15
       1       9   52428800        512        1 YES    INACTIVE          1028421 23-DEC-15     1029234 23-DEC-15

standby redo log的文件大小與primary資料庫online redo log文件大小相同。

standby redo log的組數最好比主庫online redo log多。

當前主庫有三組、每組1個member、大小為50M, 我們在備庫增加四組，每組1個member，大小為50M

StandBy>ALTER DATABASE ADD STANDBY  LOGFILE GROUP 4('/usr/oracle/standby/app/oradata/orcl_standby/stbyredolog4a.log') SIZE  50 M;

Database altered.

StandBy>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5('/usr/oracle/standby/app/oradata/orcl_standby/stbyredolog5a.log') SIZE  50 M;

Database altered.

StandBy>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6('/usr/oracle/standby/app/oradata/orcl_standby/stbyredolog6a.log') SIZE  50 M;

Database altered.

StandBy>ALTER DATABASE ADD STANDBY LOGFILE GROUP 7('/usr/oracle/standby/app/oradata/orcl_standby/stbyredolog7a.log') SIZE  50 M;

Database altered.

查詢備庫當前的日誌

Standby>select group#, type, member from v$logfile;

GROUP# TYPE      MEMBER
-----------------------------------------------------------------------------------------------------------------------------     3 ONLINE      /usr/oracle/standby/app/oradata/orcl_standby/redo03.log
ONLINE      /usr/oracle/standby/app/oradata/orcl_standby/redo02.log
ONLINE      /usr/oracle/standby/app/oradata/orcl_standby/redo01.log
STANDBY      /usr/oracle/standby/app/oradata/orcl_standby/stbyredolog4a.log
STANDBY      /usr/oracle/standby/app/oradata/orcl_standby/stbyredolog5a.log
STANDBY      /usr/oracle/standby/app/oradata/orcl_standby/stbyredolog6a.log
STANDBY      /usr/oracle/standby/app/oradata/orcl_standby/stbyredolog7a.log

為了switchover，我們在主庫也增加4組standby redo log

Primary>ALTER DATABASE ADD STANDBY   LOGFILE GROUP 4('/usr/oracle/app/oradata/orcl/stbyredolog4a.log') SIZE  50 M;

Database altered.

Primary>ALTER DATABASE ADD STANDBY   LOGFILE GROUP 5('/usr/oracle/app/oradata/orcl/stbyredolog5a.log') SIZE  50 M;

Database altered.

Primary>ALTER DATABASE ADD STANDBY   LOGFILE GROUP 6('/usr/oracle/app/oradata/orcl/stbyredolog6a.log') SIZE  50 M;

Database altered.

Primary>ALTER DATABASE ADD STANDBY   LOGFILE GROUP 7('/usr/oracle/app/oradata/orcl/stbyredolog7a.log') SIZE  50 M;

Database altered.

查看結果：

Primary>select * from v$logfile;

GROUP# STATUS      TYPE         MEMBER                        IS_REC
------ ---------- ---------- -------------------------------------------------- ------
         ONLINE     /usr/oracle/app/oradata/orcl/redo03.log        NO
         ONLINE     /usr/oracle/app/oradata/orcl/redo02.log        NO
         ONLINE     /usr/oracle/app/oradata/orcl/redo01.log        NO
         STANDBY    /usr/oracle/app/oradata/orcl/stbyredolog4a.log    NO
         STANDBY    /usr/oracle/app/oradata/orcl/stbyredolog5a.log    NO
         STANDBY    /usr/oracle/app/oradata/orcl/stbyredolog6a.log    NO
         STANDBY    /usr/oracle/app/oradata/orcl/stbyredolog7a.log    NO
rows selected.

更改primary和standby端的log_archive_dest_2、

更改Primary端的log_archive_dest_2

Primary>alter system set log_archive_dest_2='SERVICE=tns_standby LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl_standby';

System altered.

更改standby端的log_archive_dest_2

Standby>alter system set log_archive_dest_2='SERVICE=tns_primary LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl';

System altered.

啟用實時StandBy端redo應用

StandBy>alter database recover managed standby database using current logfile  disconnect from session;

驗證：

首先在Primay端插入一條數據：

Primary>select * from scott.dept;

    DEPTNO DNAME            LOC
---------- ---------------------------- --------------------------
ACCOUNTING            NEW YORK
RESEARCH            DALLAS
SALES            CHICAGO
OPERATIONS            BOSTON
OPERATIONS            OPERATIONS

Primary>INSERT INTO "SCOTT"."DEPT" ("DEPTNO", "DNAME", "LOC") VALUES ('13', 'OPERATIONS', 'OPERATIONS');
row created.

Primary>commit;

Commit complete.

standby端查看

StandBy>select * from scott.dept;
    DEPTNO DNAME            LOC
---------- ---------------------------- --------------------------
ACCOUNTING            NEW YORK
RESEARCH            DALLAS
SALES            CHICAGO
OPERATIONS            BOSTON
OPERATIONS            OPERATIONS
OPERATIONS            OPERATIONS
rows selected.

10、switchover

備庫暫停 redo應用

StandBy>alter database recover managed standby database cancel;

Database altered.

查詢主庫是否支持switchover操作

Primary> select switchover_status from v$database;

SWITCHOVER_STATUS
----------------------------------------
TO STANDBY

查詢備庫是否支持switchover操作

StandBy> select switchover_status from v$database;

SWITCHOVER_STATUS
----------------------------------------
NOT ALLOWED

not allowed是因為主庫還未變切換為standby

switchover，primary切換為物理standby，切換後查看資料庫角色、打開模式、

Primary>alter database commit to switchover to physical standby;

Database altered.

Primary>shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
Primary>startup
ORACLE instance started.

Total System Global Area 3290345472 bytes
Fixed Size            2217832 bytes
Variable Size         1795164312 bytes
Database Buffers     1476395008 bytes
Redo Buffers           16568320 bytes
Database mounted.
Database opened.
Primary>select open_mode  from v$database;

OPEN_MODE
----------------------------------------
READ ONLY

Primary>select database_role from v$database;

DATABASE_ROLE
--------------------------------
PHYSICAL STANDBY

Primary>

如果報錯ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected，關閉活動的連接即可

Primary>alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby
*
ERROR at line 1:
ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

物理standby切換為primary

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
----------------------------------------
SWITCHOVER PENDING

如果是SWITCHOVER PENDING，說明當前standby資料庫沒有啟動redo應用，重新執行下麵的命令即可

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

如果是session active 說明當前有用戶連接到StandBy資料庫，建議先斷開這些連接，或者先關閉資料庫，啟動到mount狀態（出現上面那個狀態是你用sqlplus連接著主庫，啟動到mount狀態就正常啦）

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 3290345472 bytes
Fixed Size            2217832 bytes
Variable Size         1795164312 bytes
Database Buffers     1476395008 bytes
Redo Buffers           16568320 bytes
Database mounted.
SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
----------------------------------------
TO PRIMARY

切換

StandBy>select switchover_status from v$database;

SWITCHOVER_STATUS
----------------------------------------
TO PRIMARY

StandBy> alter database commit to switchover to primary;

Database altered.

StandBy>alter database open;

Database altered.

StandBy>select open_mode from v$database;

OPEN_MODE
----------------------------------------
READ WRITE

StandBy>select database_role from v$database;

DATABASE_ROLE
--------------------------------
PRIMARY

StandBy>

測試同上（插入刪除），這裡只進行簡單測試

現在的Primay切換日誌

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------

SQL> alter system switch logfile;

System altered.

在現在的standby查詢

SYS@orcl>select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
        15

常用查詢

1、查詢進程的活動狀態

select process,client_process,sequence#,status from v$managed_standby;

2、查詢redo應用進度

select dest_name,archived_thread#,archived_seq#,applied_thread#,applied_seq#,db_unique_name from v$archive_dest_status where status ='VALID';

3、查詢歸檔文件路徑及創建信息

select name,creator,sequence#,applied,completion_time from v$archived_log;

4、查詢歸檔歷史

select first_time,first_change#,next_change#,sequence# from v$log_history;

select thread#,sequence#,applied from v$archived_log;

5、參看資料庫的基本信息

select database_role,db_unique_name,open_mode,protection_mode,protection_level,switchover_status from v$database;

6、查詢redo應用及redo傳輸服務的活動狀態

select database_role,db_unique_name,open_mode,protection_mode,protection_level,switchover_status from v$database;

7、檢查應用模式

StandBy>select recovery_mode from v$archive_dest_status where dest_id=2;

RECOVERY_MODE
----------------------------------------------
IDLE

取值

idle

managed：

managed real_time_apply：

8、在主庫上執行只主要是查看日誌歸檔目的地是否可用，如果遠程歸檔目錄不可用則error會顯示錯誤信息

Primary>select dest_name,status,error from v$archive_dest;

DEST_NAME          STATUS     ERROR
------------------------- ---------- ----------------------------------------------------------------------------------------------------
LOG_ARCHIVE_DEST_1      VALID
LOG_ARCHIVE_DEST_2      VALID
LOG_ARCHIVE_DEST_3      INACTIVE
LOG_ARCHIVE_DEST_4      INACTIVE
LOG_ARCHIVE_DEST_5      INACTIVE
LOG_ARCHIVE_DEST_6      INACTIVE
LOG_ARCHIVE_DEST_7      INACTIVE
LOG_ARCHIVE_DEST_8      INACTIVE
LOG_ARCHIVE_DEST_9      INACTIVE
LOG_ARCHIVE_DEST_10      INACTIVE
LOG_ARCHIVE_DEST_11      INACTIVE
LOG_ARCHIVE_DEST_12      INACTIVE
LOG_ARCHIVE_DEST_13      INACTIVE
LOG_ARCHIVE_DEST_14      INACTIVE
LOG_ARCHIVE_DEST_15      INACTIVE
LOG_ARCHIVE_DEST_16      INACTIVE
LOG_ARCHIVE_DEST_17      INACTIVE
LOG_ARCHIVE_DEST_18      INACTIVE
LOG_ARCHIVE_DEST_19      INACTIVE
LOG_ARCHIVE_DEST_20      INACTIVE
LOG_ARCHIVE_DEST_21      INACTIVE
LOG_ARCHIVE_DEST_22      INACTIVE
LOG_ARCHIVE_DEST_23      INACTIVE
LOG_ARCHIVE_DEST_24      INACTIVE
LOG_ARCHIVE_DEST_25      INACTIVE
LOG_ARCHIVE_DEST_26      INACTIVE
LOG_ARCHIVE_DEST_27      INACTIVE
LOG_ARCHIVE_DEST_28      INACTIVE
LOG_ARCHIVE_DEST_29      INACTIVE
LOG_ARCHIVE_DEST_30      INACTIVE
LOG_ARCHIVE_DEST_31      INACTIVE
STANDBY_ARCHIVE_DEST      VALID
rows selected.

9、查詢歸檔日誌的應用情況。主備庫上查詢結果不同，在主庫上對於每個歸檔文件會有兩條記錄。

View Code

10、查詢dataguard狀態信息

Standby>select message_num,message from v$dataguard_status;

MESSAGE_NUM MESSAGE
----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ARC0: Archival started
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC2: Becoming the heartbeat ARCH
ARC3: Archival started
RFS[1]: Assigned to RFS process 5881
RFS[1]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[2]: Assigned to RFS process 5892
RFS[2]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[3]: Assigned to RFS process 5897
RFS[3]: Identified database type as 'physical standby': Client is ARCH pid 9101
Attempt to start background Managed Standby Recovery process
MRP0: Background Managed Standby Recovery process started
Managed Standby Recovery not using Real Time Apply
Media Recovery Log /usr/oracle/standby/app/flash_recovery_area/ORCL_STANDBY/archivelog/2015_12_30/o1_mf_1_25_c87mn8sw_.arc
Media Recovery Waiting for thread 1 sequence 26
RFS[4]: Assigned to RFS process 5906
RFS[4]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[5]: Assigned to RFS process 5911
RFS[5]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[6]: Assigned to RFS process 5918
RFS[6]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[7]: Assigned to RFS process 5925
RFS[7]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[8]: Assigned to RFS process 5932
RFS[8]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[9]: Assigned to RFS process 5938
RFS[9]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[10]: Assigned to RFS process 5945
RFS[10]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[11]: Assigned to RFS process 5952
RFS[11]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[12]: Assigned to RFS process 5959
RFS[12]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[13]: Assigned to RFS process 5970
RFS[13]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[14]: Assigned to RFS process 5976
RFS[14]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[15]: Assigned to RFS process 5984
RFS[15]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[16]: Assigned to RFS process 5991
RFS[16]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[17]: Assigned to RFS process 6006
RFS[17]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[18]: Assigned to RFS process 6018
RFS[18]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[19]: Assigned to RFS process 6024
RFS[19]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[20]: Assigned to RFS process 6035
RFS[20]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[21]: Assigned to RFS process 6047
RFS[21]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[22]: Assigned to RFS process 6059
RFS[22]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[23]: Assigned to RFS process 6070
RFS[23]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[24]: Assigned to RFS process 6076
RFS[24]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[25]: Assigned to RFS process 6082
RFS[25]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[26]: Assigned to RFS process 6090
RFS[26]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[27]: Assigned to RFS process 6097
RFS[27]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[28]: Assigned to RFS process 6104
RFS[28]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[29]: Assigned to RFS process 6110
RFS[29]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[30]: Assigned to RFS process 6116
RFS[30]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[31]: Assigned to RFS process 6123
RFS[31]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[32]: Assigned to RFS process 6133
RFS[32]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[33]: Assigned to RFS process 6144
RFS[33]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[34]: Assigned to RFS process 6161
RFS[34]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[35]: Assigned to RFS process 6167
RFS[35]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[36]: Assigned to RFS process 6174
RFS[36]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[37]: Assigned to RFS process 6181
RFS[37]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[38]: Assigned to RFS process 6188
RFS[38]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[39]: Assigned to RFS process 6201
RFS[39]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[40]: Assigned to RFS process 6207
RFS[40]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[41]: Assigned to RFS process 6214
RFS[41]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[42]: Assigned to RFS process 6221
RFS[42]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[43]: Assigned to RFS process 6232
RFS[43]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[44]: Assigned to RFS process 6238
RFS[44]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[45]: Assigned to RFS process 6244
RFS[45]: Identified database type as 'physical standby': Client is ARCH pid 9099
MRP0: Background Media Recovery cancelled with status 16037
MRP0: Background Media Recovery process shutdown
Managed Standby Recovery Canceled
RFS[46]: Assigned to RFS process 6253
RFS[46]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[47]: Assigned to RFS process 6263
RFS[47]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[48]: Assigned to RFS process 6270
RFS[48]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[49]: Assigned to RFS process 6276
RFS[49]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[50]: Assigned to RFS process 6284
RFS[50]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[51]: Assigned to RFS process 6295
RFS[51]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[52]: Assigned to RFS process 6301
RFS[52]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[53]: Assigned to RFS process 6312
RFS[53]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[54]: Assigned to RFS process 6320
RFS[54]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[55]: Assigned to RFS process 6325
RFS[55]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[56]: Assigned to RFS process 6332
RFS[56]: Identified database type as 'physical standby': Client is LGWR ASYNC pid 10167
Primary database is in MAXIMUM PERFORMANCE mode
ARC0: Beginning to archive thread 1 sequence 26 (1039145-1040585)
RFS[57]: Assigned to RFS process 6334
RFS[57]: Identified database type as 'physical standby': Client is LGWR ASYNC pid 10167
ARC0: Completed archiving thread 1 sequence 26 (0-0)
Primary database is in MAXIMUM PERFORMANCE mode
RFS[58]: Assigned to RFS process 6339
RFS[58]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[59]: Assigned to RFS process 6356
RFS[59]: Identified database type as 'physical standby': Client is ARCH pid 9099
Attempt to start background Managed Standby Recovery process
MRP0: Background Managed Standby Recovery process started
Managed Standby Recovery starting Real Time Apply
Media Recovery Log /usr/oracle/standby/app/flash_recovery_area/ORCL_STANDBY/archivelog/2015_12_30/o1_mf_1_26_c87po9r6_.arc
Media Recovery Waiting for thread 1 sequence 27 (in transit)
RFS[60]: Assigned to RFS process 6364
RFS[60]: Identified database type as 'physical standby': Client is ARCH pid 9099
MRP0: Background Media Recovery cancelled with status 16037
Managed Standby Recovery not using Real Time Apply
MRP0: Background Media Recovery process shutdown
Managed Standby Recovery Canceled
RFS[61]: Assigned to RFS process 6373
RFS[61]: Identified database type as 'physical standby': Client is ARCH pid 9099
ARC1: Beginning to archive thread 1 sequence 27 (1040585-1040839)
ARC1: Completed archiving thread 1 sequence 27 (0-0)
RFS[62]: Assigned to RFS process 6378
RFS[62]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[63]: Assigned to RFS process 6382
RFS[63]: Identified database type as 'physical standby': Client is Foreground pid 9102
ARC3: Beginning to archive thread 1 sequence 28 (1040839-1040863)
ARC3: Completed archiving thread 1 sequence 28 (0-0)
RFS[64]: Assigned to RFS process 6386
RFS[64]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[65]: Assigned to RFS process 6393
RFS[65]: Identified database type as 'physical standby': Client is ARCH pid 9095
ARC0: Beginning to archive thread 1 sequence 29 (1040863-1040945)
ARC0: Completed archiving thread 1 sequence 29 (0-0)
RFS[66]: Assigned to RFS process 6395
RFS[66]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[67]: Assigned to RFS process 6400
RFS[67]: Identified database type as 'physical standby': Client is Foreground pid 9102
ARC1: Beginning to archive thread 1 sequence 30 (1040945-1040959)
ARC1: Completed archiving thread 1 sequence 30 (0-0)
RFS[68]: Assigned to RFS process 6404
RFS[68]: Identified database type as 'physical standby': Client is Foreground pid 9102
RFS[69]: Assigned to RFS process 6406
RFS[69]: Identified database type as 'physical standby': Client is Foreground pid 9102
Attempt to start background Managed Standby Recovery process
MRP0: Background Managed Standby Recovery process started
Managed Standby Recovery starting Real Time Apply
Media Recovery Log /usr/oracle/standby/app/flash_recovery_area/ORCL_STANDBY/archivelog/2015_12_30/o1_mf_1_27_c87pwj5t_.arc
Media Recovery Log /usr/oracle/standby/app/flash_recovery_area/ORCL_STANDBY/archivelog/2015_12_30/o1_mf_1_28_c87px4hz_.arc
Media Recovery Log /usr/oracle/standby/app/flash_recovery_area/ORCL_STANDBY/archivelog/2015_12_30/o1_mf_1_29_c87pzfbv_.arc
Media Recovery Log /usr/oracle/standby/app/flash_recovery_area/ORCL_STANDBY/archivelog/2015_12_30/o1_mf_1_30_c87pzr9s_.arc
Media Recovery Log /usr/oracle/standby/app/flash_recovery_area/ORCL_STANDBY/archivelog/2015_12_30/o1_mf_1_31_c87pzsk7_.arc
Resetting standby activation ID 1427590416 (0x55174d10)
MRP0: Media Recovery Complete: End-Of-REDO
MRP0: Background Media Recovery process shutdown
ALTER DATABASE SWITCHOVER TO PRIMARY
ARC0: Becoming the 'no SRL' ARCH
ARC0: Becoming the 'no SRL' ARCH
ARC1: Becoming the 'no SRL' ARCH
rows selected.

11、查看是否有日誌缺失

Standby>select * from v$archive_gap;

no rows selected

主備庫的起停和維護

Dataguard關閉（先關主庫再關備庫）

Dataguard開啟（先開備庫再開主庫）

Failover切換方法

(1)判斷主資料庫確實出現嚴重的硬體故障或其他原因導致主資料庫無法啟動。

(2)在物理備用資料庫上檢查是否有archive redo log gaps

Standby>SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

no rows selected

如果有則消除archive redo log gaps

從主資料庫上或其他備份的地方把沒有傳到物理備用資料庫的archive redo log傳到物理備用資料庫上，並註冊到物理備用資料庫的controlfile中。

SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'archive redo log文件名稱';

重覆2,3步驟直到V$ARCHIVE_GAP視圖無記錄存在。

(3)查看歸檔文件是否完整

主庫備庫分別執行下麵語句，取得各個線程已經歸檔的文件最大的序號，如果最大序號不同，則必須將多出的序號對應的歸檔文件複製到待轉換的standby伺服器（也有可能primary庫已經無法打開了）

SQL> select distinct  thread#, max(sequence#)  over  (partition by  thread#) from v$archived_log;

   THREAD#   MAX(SEQUENCE#)OVER(PARTITIONBYTHREAD#)
---------- --------------------------------------
                      84

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
        84

(4）在物理備用資料庫上發起failover操作

SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;

(5)把物理備用資料庫轉化成主用角色

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

(6)把新的主用資料庫重新啟動

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP;

(7)對新的主用資料庫做全備份.