rac環境下修改歸檔路徑，並設定遠端傳輸屬性

1：設定rac資料庫為歸檔模式

在節點2上檢視rac的狀態

[oracle@rac2 dbs]$ crs_stat -t
Name           Type           Target    State     Host       
------------------------------------------------------------
ora....SM1.asm application    ONLINE    ONLINE    rac1       
ora....C1.lsnr application    ONLINE    ONLINE    rac1       
ora.rac1.gsd   application    ONLINE    ONLINE    rac1       
ora.rac1.ons   application    ONLINE    ONLINE    rac1       
ora.rac1.vip   application    ONLINE    ONLINE    rac1       
ora....SM2.asm application    ONLINE    ONLINE    rac2       
ora....C2.lsnr application    ONLINE    ONLINE    rac2       
ora.rac2.gsd   application    ONLINE    ONLINE    rac2       
ora.rac2.ons   application    ONLINE    ONLINE    rac2       
ora.rac2.vip   application    ONLINE    ONLINE    rac2       
ora.racdb.db   application    ONLINE    ONLINE    rac1       
ora....b1.inst application    ONLINE    ONLINE    rac1       
ora....b2.inst application    ONLINE    ONLINE    rac2 

[oracle@rac2 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 20 21:49:33 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> select inst_id,instance_name,version,archiver,status from  gv$instance;

   INST_ID INSTANCE_NAME    VERSION           ARCHIVE STATUS
---------- ---------------- ----------------- ------- ------------
         1 racdb1           10.2.0.1.0        STOPED MOUNTED
         2 racdb2           10.2.0.1.0        STOPED MOUNTED

因此以上資訊可以看到rac1和rac2節點的資料庫均處於mount狀態，但還處於未歸檔（archive欄位的狀態為stoped）

rac跟單例項一樣，修改歸檔模式是在db處於mount狀態下進行，但rac的所有例項必須處於非open狀態，
然後在任意一個處於mount狀態的例項（節點）執行alter database命令，操作如下

在rac2節點執行

首先備份spfile

SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /home/oracle/10.2.0/db_1/dbs/spfileracdb2.ora
SQL>
SQL> create pfile='/home/oracle/initbak.0521' from spfile;

File created.

接著修改叢集引數

SQL> alter system set cluster_database=false scope=spfile sid='*';

System altered.

v$parameter檢視中 issys_modifiable可看出哪些引數是靜態、動態

 

將rac2節點的db啟動到mount狀態，rac1節點保持db處於mount狀態（如果是close狀態的話，rac1節點是歸檔，但是沒有路徑）

 

在rac2節點上修改歸檔路徑，並修改歸檔模式

SQL> alter system set log_archive_dest_1='location=/home/oracle/rac2_arch' sid='racdb2';

System altered.

SQL>  alter system set log_archive_dest_1='location=/home/oracle/rac1_arch' sid='racdb1';

System altered.

SQL> alter database archivelog;

Database altered.

rac2節點上開啟db，檢視歸檔及路徑

SQL> alter database open;

Database altered.

SQL>
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/rac2_arch
Oldest online log sequence     32
Next log sequence to archive   33
Current log sequence           33

然後在rac2節點上改回原來的引數cluster_database為true狀態
注意此引數一定要在rac2節點的db處於open狀態改，修改完之後重啟db

SQL> alter system set cluster_database=true scope=spfile sid='*';

System altered.

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

Total System Global Area  608174080 bytes
Fixed Size                  1220844 bytes
Variable Size             239079188 bytes
Database Buffers          364904448 bytes
Redo Buffers                2969600 bytes
Database mounted.
Database opened.
SQL>  show parameter cluster_database;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2

 

然後在rac1節點啟動db，檢視歸檔

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/rac1_arch
Oldest online log sequence     41
Next log sequence to archive   42
Current log sequence           42
SQL>

 

然後切換日誌，檢視新的日誌檔案

 

[oracle@rac2 rac2_arch]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue May 21 02:39:04 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> alter system switch logfile;

System altered.

SQL>
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
[oracle@rac2 rac2_arch]$ ls -lt
total 25112
-rw-r-----  1 oracle dba 25679872 May 21 02:39 2_33_797085894.dbf
[oracle@rac2 rac2_arch]$ date
Tue May 21 02:39:23 CST 2013
[oracle@rac2 rac2_arch]$
[oracle@rac2 rac2_arch]$ pwd
/home/oracle/rac2_arch
[oracle@rac2 rac2_arch]$

 

 

 

2：配置節點間歸檔日誌自動傳輸

透過rman進行rac備份時，必須確保連線到的例項能夠訪問所有節點生成的歸檔日誌檔案，否則會導致
備份失敗（除非不備份歸檔日誌，這裡有兩種方法：

*各節點生成的歸檔日誌存放在共享儲存上（比如asm或第三方的叢集檔案系統）
*各節點除在本地生成歸檔日誌外，另外向其他節點或者說執行備份的節點傳送歸檔日誌，以確保執行
 備份的的那臺節點能夠訪問到所有的歸檔日誌檔案
 
 
 oracle的重做日誌傳送機制非常靈活，其實就是給 log_archive_dest_n初始化引數設定適當的值。
 
 此次決定將備份操作放在節點rac2上執行，因此需要在rac1節點中，設定傳送節點rac1生成的歸檔到節點rac2即可
 
 rac1節點執行

檢視網路配置
 
[oracle@rac1 rac1_arch]$ cd $ORACLE_HOME/network/admin

[oracle@rac1 admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /home/oracle/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

RACDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = racdb)
      (INSTANCE_NAME = racdb1)
    )
  )

RACDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))
#    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = racdb)
    )
  )

LISTENERS_RACDB =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

RACDB2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = racdb)
      (INSTANCE_NAME = racdb2)
    )
  )

[oracle@rac1 admin]$ tnsping racdb2

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 21-MAY-2013 02:37:30

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = racdb) (INSTANCE_NAME = racdb2)))
OK (10 msec)
[oracle@rac1 admin]$

然後設定歸檔日誌自動傳輸路徑

[oracle@rac1 admin]$ sqlplus  / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue May 21 02:39:09 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> alter system set log_archive_dest_2='service=RACDB2' sid='racdb1';

System altered.

SQL>

注意此處service的值RACDB2，是 tnsnames.ora中的網路服務命令

檢視設定後的新值

SQL> show parameter log_archive_dest;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest                     string
log_archive_dest_1                   string      location=/home/oracle/rac1_arc
                                                 h
log_archive_dest_10                  string
log_archive_dest_2                   string      service=RACDB2
log_archive_dest_3                   string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_6                   string
log_archive_dest_7                   string
log_archive_dest_8                   string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_9                   string
log_archive_dest_state_1             string      enable
log_archive_dest_state_10            string      enable
log_archive_dest_state_2             string      enable
log_archive_dest_state_3             string      enable
log_archive_dest_state_4             string      enable
log_archive_dest_state_5             string      enable
log_archive_dest_state_6             string      enable
log_archive_dest_state_7             string      enable
log_archive_dest_state_8             string      enable
log_archive_dest_state_9             string      enable

然後在rac1節點執行手動切換

SQL>  select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         44   52428800          2 YES ACTIVE                 3380998 21-MAY-13
         2          1         45   52428800          2 NO  CURRENT                3381074 21-MAY-13
         3          2         33   52428800          2 YES INACTIVE               3362646 20-MAY-13
         4          2         34   52428800          2 NO  CURRENT                3378938 21-MAY-13

SQL>  alter system switch logfile;

System altered.

SQL> col name format a55;

SQL> select inst_id,recid,dest_id,name from gv$archived_log where sequence#=45;

   INST_ID      RECID    DEST_ID NAME
---------- ---------- ---------- -------------------------------------------------------
         1         72          1 /home/oracle/rac1_arch/1_45_797085894.dbf
         1         73          2 /home/oracle/rac2_arch/1_45_797085894.dbf
         2         72          1 /home/oracle/rac1_arch/1_45_797085894.dbf
         2         73          2 /home/oracle/rac2_arch/1_45_797085894.dbf

 

可以看到歸檔日誌成功生成併傳送到rac2節點

[oracle@rac2 rac2_arch]$ ls -lt
total 28148
-rw-r-----  1 oracle dba   643072 May 21 03:33 2_34_797085894.dbf
-rw-r-----  1 oracle dba    10240 May 21 03:33 1_45_797085894.dbf
-rw-r-----  1 oracle dba     2048 May 21 03:31 1_44_797085894.dbf
-rw-r-----  1 oracle dba  2440192 May 21 03:28 1_43_797085894.dbf
-rw-r-----  1 oracle dba 25679872 May 21 02:39 2_33_797085894.dbf

如果在rac1節點一直切換日誌，但是在rac2節點仍看不到切換後的日誌，那麼就在rac1節點執行如下操作：

SQL> alter system set log_archive_local_first=false sid='racdb1';

System altered.

LOG_ARCHIVE_LOCAL_FIRST引數，用來設定是否首先歸檔檔案到本地，預設為true，將其改為false，只修改節點1的設定即可

 

 現在考慮一個問題，加入由於某個原因，rac1節點的日誌檔案沒有傳送到rac2節點上，怎麼辦？
 
 首先在rac1節點上找到歸檔日誌檔案的全路徑（從v$archived_log檢視得到），然後透過scp遠端複製到
 rac2節點上，然後再在rac2節點註冊該歸檔日誌，註冊命令為
 SQL> alter database register physical logfile '/home/oracle/rac2_arch/1_46_797085894.dbf';

 

 

STANDBY_ARCHIVE_DEST is relevant only for a standby database in managed recovery mode. It specifies the location of archive logs arriving from a primary database.

 

LOG_ARCHIVE_LOCAL_FIRST是10g的新功能
它的推出主要是在10g以前dataguard環境中本地和遠端的歸檔需要都完成以後，online redo log才可以被重用，這就意味著在網路速度慢的環境中，dataguard會很大地影響priamry site的處理能力。

而設定了LOG_ARCHIVE_LOCAL_FIRST=TRUE，則表示在本地歸檔結束以後，再將重作資料傳到遠端，而設定為FALSE，則表示在歸檔到本地的同時也傳送資料到遠端。