oracle實驗記錄 (oracle 10G dataguard(7)physical dg轉換)

physical dg 角色轉換
SQL> select table_name from dict where table_name like '%DATAGUARD%';

TABLE_NAME
------------------------------
V$DATAGUARD_STATUS
V$DATAGUARD_CONFIG
V$DATAGUARD_STATS
GV$DATAGUARD_STATUS
GV$DATAGUARD_CONFIG

SQL> desc v$dataguard_stats;
 名稱                                      是否為空? 型別
 ----------------------------------------- -------- --------------------------

 NAME                                               VARCHAR2(32)
 VALUE                                              VARCHAR2(64)
 UNIT                                               VARCHAR2(30)
 TIME_COMPUTED                                      VARCHAR2(30)

NAME VARCHAR2(32) The parameter name can be any of the following:
    APPLY FINISH TIME: Estimated time before log apply services will finish applying the redo data available on the standby database. If there are gaps in     the log files, this parameter shows the time it will take to resolve the gap with the lowest SCN.

    APPLY LAG: Amount of time that the application of redo data on the standby database lags behind the primary database.

    TRANSPORT LAG: How much redo data (in time units) generated by the primary database is not available or applicable on the standby database at the time of     computation.

    Note: This parameter includes the redo data that has not been transmitted to the standby database and redo data that is available on the standby database     but has not been applied yet, such as the redo data for resolving a gap.

    ESTIMATED STARTUP TIME: Estimated time the physical standby database was started. This is not displayed for logical standby databases.

   STANDBY HAS BEEN OPEN: Indicates if the physical standby database was opened in read-only mode at any time since the last time the database was started.      The parameter value indicates if the instance must be stopped and restarted after a failover. If the physical standby database has been opened in read-       only mode since the last time it was started and it is the target of a failover, you must shut down the database and restart it in read/write mode. If the     primary database must be shutdown and restarted, use the sum of APPLY FINISH TIME and ESTIMATED STARTUP TIME to obtain an estimated failover time (the         estimate assumes SHUTDOWN ABORT).
 
VALUE VARCHAR2(64) Value of the parameter. For example, the APPLY FINISH TIME parameter value +00 00:00:01.7 indicates the standby database needs 1.7 seconds to finish applying the remaining redo data.
UNIT VARCHAR2(30) Time unit for the parameter, displayed as day (2) to second (n) interval, where n is 0 or 1.
TIME_COMPUTED VARCHAR2(30) Local time on the standby database when the value of the parameter is computed or sampled.

 

 

SQL> col name format a20
SQL> col value format a15
SQL> col time_computed format a20

SQL> col unit format a10
SQL> select * from v$dataguard_stats;

NAME                 VALUE           UNIT       TIME_COMPUTED
-------------------- --------------- ---------- --------------------
apply finish time    +00 00:00:06.3  day(2) to  10-11月-2009 13:51:5
                                     second(1)  4
                                     interval

apply lag            +00 02:50:37    day(2) to  10-11月-2009 13:51:5
                                     second(0)  4
                                     interval

estimated startup ti 13              second     10-11月-2009 13:51:5
me                                              4

NAME                 VALUE           UNIT       TIME_COMPUTED
-------------------- --------------- ---------- --------------------
standby has been ope N                          10-11月-2009 13:51:5
n                                               4

transport lag        +00 00:00:00    day(2) to  10-11月-2009 13:51:5
                                     second(0)  4
                                     interval

分析
apply finish time :oracle認為應用完現在的日誌需要6S
apply lag ：在過去2小時 50分中PRIMARY產生傳輸的redo 都沒應用

 

當角色切換時候 將出現一個 db_role_change的事件(與startup,shutdown ,database error 一樣) 是4種系統事件

audit 對於 sysdba sysoper   startup,shutdown conn 等操作  defautlt 都在 記錄
win下 在事件檢視器中可以看
unix 在 $ORACLE_HOME/rdbms/audit
若想啟動對sysdba or sysoper 的其他審記 則
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_sys_operations                 boolean     FALSE  改為true(default false)

 

檢查 是否可以切換

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;(primary)

SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE

primary應該為to standby，如果不是檢查是否引數配置有問題，或 若是sessions active 則需要加witch session shutdown (關閉所有活動會話)

 

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

資料庫已更改。

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

PROCESS   CLIENT_P STATUS        SEQUENCE#
--------- -------- ------------ ----------
ARCH      ARCH     CLOSING             110
ARCH      ARCH     CLOSING             111
RFS       LGWR     IDLE                112
RFS       ARCH     IDLE                  0
RFS       UNKNOWN  IDLE                  0
MRP0      N/A      WAIT_FOR_LOG        112

已選擇6行。

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
NOT ALLOWED～～～～～～～～～～～～需要primary先切換

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY with session shutdo   (primary)
wn;

資料庫已更改。

這句命令的意義：
1.shutdown primary,終止所有活動會話,shutdown 會產生一個full checkpoint
2.傳任何未歸檔的redo(primary online redo file )或者primary 還未傳到standby的archivedlog到 standby並應用，這是為了和primary 同步一致
3.新增重做終止標記到要歸檔的最後一個日誌檔案頭（primary）
4.建立current controlfile備份
5.將current controlfile轉換為standby controlfile

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;(standby )

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

SQL> alter database commit to switchover to primary;(standby)

資料庫已更改。

SQL> select db_unique_name,database_role ,open_mode from v$database
select db_unique_name,database_role ,open_mode from v$database
                                                    *
第 1 行出現錯誤:
ORA-01507: 未裝載資料庫
SQL> alter database mount;
alter database mount
*
第 1 行出現錯誤:
ORA-01100: 資料庫已裝載

SQL> alter database open;
alter database open
*
第 1 行出現錯誤:
ORA-01507: 未裝載資料庫

切換完後 都是mount狀態 但需要重起 ,startup force，如果physiacal  standby自從上次啟動過後曾經以只讀模式開啟，必須關閉目標備資料
庫並重啟

SQL> select db_unique_name,database_role ,open_mode from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
------------------------------ ---------------- ----------
xh                             PHYSICAL STANDBY READ ONLY

SQL> select db_unique_name,database_role ,open_mode from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
------------------------------ ---------------- ----------
standby1                       PRIMARY          READ WRITE

SQL> show parameter log_archive_dest_2  (primary standby1)

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      service=xh valid_for=(online_l
                                                 ogfiles,primary_role) db_uniqu
                                                 e_name=xh
SQL> select * from t2; (primary standby1)

         A
----------
         1
         3
         2
         4
         5
         5
         6
       888
    888888
    999999

已選擇10行。

SQL> delete from t2; (primary standby1)

已刪除10行。

SQL> commit; (primary standby1)

提交完成。

SQL> alter system switch logfile; (primary standby1)

系統已更改。

 

SQL> select process,client_process,status,sequence# from v$managed_standby;(standby  xh)

PROCESS   CLIENT_P STATUS        SEQUENCE#
--------- -------- ------------ ----------
ARCH      ARCH     CONNECTED             0
ARCH      ARCH     CONNECTED             0
RFS       UNKNOWN  IDLE                  0

SQL> alter database recover managed standby database disconnect from session; (standby  xh)

資料庫已更改。

SQL> select process,client_process,status,sequence# from v$managed_standby; (standby  xh)

PROCESS   CLIENT_P STATUS        SEQUENCE#
--------- -------- ------------ ----------
ARCH      ARCH     CONNECTED             0
ARCH      ARCH     CONNECTED             0
RFS       UNKNOWN  IDLE                  0
MRP0      N/A      WAIT_FOR_LOG          0

SQL> alter database recover managed standby database cancel;(standby  xh)

資料庫已更改。

SQL> alter database open;(standby  xh)

資料庫已更改。

SQL> select *from t2;(standby  xh)

未選定行

關於有損切換
primary突然崩潰
1.首先查詢v$archive_gap 看 是否崩潰時候有未傳送到standby 的archivedlog
有的話，手動複製過來(v$archive_gap中low_sequence#-high_sequence# 這之間的都考過來)
alter database register physical logfile 'xxxxx' 手工註冊到standby
2.可以通過v$archived_log.sequence# 檢視 standby的最高 archive log sequence#，將primary 中未傳送到standby的 archived log 都 手動複製過來 並註冊
3.在standby 執行alter database recover managed standby finish force
4.轉換physical standby 到primary
alter database commit to switchover to primary
（這中轉換的結果是單方向的，這個physical standby不能夠在轉換成 standby ，故障轉移新增一個重做結束的標識到最後一個歸檔的日誌檔案的頭部，併傳送重做到所
有允許的對於主資料庫有效的目的地（使用VALID_FOR=(PRIMARY_ROLE, *_LOGFILES)
或VALID_FOR=(ALL_ROLE, *_LOGFILES)屬性指定））
5.如果這個standby(現在轉換成的primary)以前未被 read only mode開啟過
可以執行alter database open 開啟這個新 primary
或shutdown immediate startup重啟這個新primary

 

 

關於primary重新命名檔案

看下環境

SQL>  select db_unique_name,database_role ,open_mode from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
------------------------------ ---------------- ----------
standby1                       PHYSICAL STANDBY READ ONLY

SQL>  select db_unique_name,database_role ,open_mode from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
------------------------------ ---------------- ----------
xh                             PRIMARY          READ WRITE

SQL> select name from v$datafile;(primary)

NAME
--------------------------------------------------------------------------------

G:\ORACLE\PRODUCT\10.2.0\ORADATA\XH\SYSTEM01.DBF
G:\ORACLE\PRODUCT\10.2.0\ORADATA\XH\UNDOTBS01.DBF
G:\ORACLE\PRODUCT\10.2.0\ORADATA\XH\SYSAUX01.DBF
G:\ORACLE\PRODUCT\10.2.0\ORADATA\XH\USERS01.DBF
G:\ORACLE\PRODUCT\10.2.0\ORADATA\XH\EXAMPLE01.DBF
G:\ORACLE\PRODUCT\10.2.0\ORADATA\XH\XHTR.DBF
G:\XHTR2.DBF
G:\XHTR3.DBF
G:\XHTR4.DBF
G:\ORACLE\PRODUCT\10.2.0\ORADATA\XH\XHTR5.DBF

已選擇10行。

SQL> select name from v$datafile; (standby)

NAME
--------------------------------------------------------------------------------

E:\STANDBY\DATAFILE\SYSTEM01.DBF
E:\STANDBY\DATAFILE\UNDOTBS01.DBF
E:\STANDBY\DATAFILE\SYSAUX01.DBF
E:\STANDBY\DATAFILE\USERS01.DBF
E:\STANDBY\DATAFILE\EXAMPLE01.DBF
E:\STANDBY\DATAFILE\XHTR.DBF
E:\STANDBY\DATAFILE\XHTR2.DBF
E:\STANDBY\DATAFILE\XHTR3
E:\STANDBY\DATAFILE\STANDBY1\DATAFILE\O1_MF_XHTR4_5H2778OZ_.DBF
E:\STANDBY\DATAFILE\STANDBY1\DATAFILE\O1_MF_XHTR5_5H28JPVT_.DBF

已選擇10行。

SQL> show parameter standby_fi (standby)

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      AUTO

SQL> alter tablespace xhtr offline;(primary)

表空間已更改。

SQL> host copy G:\ORACLE\PRODUCT\10.2.0\ORADATA\XH\XHTR.DBF G:\ORACLE\PRODUCT\10       (primary)
.2.0\ORADATA\XH\XHTRM.DBF
已複製         1 個檔案。

SQL> alter tablespace xhtr rename datafile 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\XH\        (primary)
XHTR.DBF' to 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\XH\XHTRM.DBF'
  2  ;

表空間已更改。

SQL> alter tablespace xhtr online;  (primary)

表空間已更改。

SQL> select name from v$datafile; (primary)

NAME
--------------------------------------------------------------------------------

G:\ORACLE\PRODUCT\10.2.0\ORADATA\XH\SYSTEM01.DBF
G:\ORACLE\PRODUCT\10.2.0\ORADATA\XH\UNDOTBS01.DBF
G:\ORACLE\PRODUCT\10.2.0\ORADATA\XH\SYSAUX01.DBF
G:\ORACLE\PRODUCT\10.2.0\ORADATA\XH\USERS01.DBF
G:\ORACLE\PRODUCT\10.2.0\ORADATA\XH\EXAMPLE01.DBF
G:\ORACLE\PRODUCT\10.2.0\ORADATA\XH\XHTRM.DBF*****************主庫rename datafile
G:\XHTR2.DBF
G:\XHTR3.DBF
G:\XHTR4.DBF
G:\ORACLE\PRODUCT\10.2.0\ORADATA\XH\XHTR5.DBF

已選擇10行。

此時 standby需要也手動指向那個， standby_file_management為auto也沒用
SQL> startup force mount
ORACLE 例程已經啟動。

Total System Global Area  289406976 bytes
Fixed Size                  1248576 bytes
Variable Size              79692480 bytes
Database Buffers          201326592 bytes
Redo Buffers                7139328 bytes
資料庫裝載完畢。
SQL> host copy E:\STANDBY\DATAFILE\XHTR.DBF  E:\STANDBY\DATAFILE\XHTRM.DBF
已複製         1 個檔案。

SQL> alter database rename file 'E:\STANDBY\DATAFILE\XHTR.DBF' to 'E:\STANDBY\DA
TAFILE\XHTRM.DBF'
  2  ;
alter database rename file 'E:\STANDBY\DATAFILE\XHTR.DBF' to 'E:\STANDBY\DATAFIL
E\XHTRM.DBF'
*
第 1 行出現錯誤:
ORA-01511: 重新命名日誌/資料檔案時出錯
ORA-01275: 自動進行備用檔案管理時, 不允許進行 RENAME 操作。

SQL> alter system set standby_file_management='MANUAL';

系統已更改。

SQL> alter database rename file 'E:\STANDBY\DATAFILE\XHTR.DBF' to 'E:\STANDBY\DA
TAFILE\XHTRM.DBF'
  2  ;

資料庫已更改。

SQL> alter system set standby_file_management='auto';

系統已更改。

SQL> alter database open;

資料庫已更改。

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------

E:\STANDBY\DATAFILE\SYSTEM01.DBF
E:\STANDBY\DATAFILE\UNDOTBS01.DBF
E:\STANDBY\DATAFILE\SYSAUX01.DBF
E:\STANDBY\DATAFILE\USERS01.DBF
E:\STANDBY\DATAFILE\EXAMPLE01.DBF
E:\STANDBY\DATAFILE\XHTRM.DBF
E:\STANDBY\DATAFILE\XHTR2.DBF
E:\STANDBY\DATAFILE\XHTR3
E:\STANDBY\DATAFILE\STANDBY1\DATAFILE\O1_MF_XHTR4_5H2778OZ_.DBF
E:\STANDBY\DATAFILE\STANDBY1\DATAFILE\O1_MF_XHTR5_5H28JPVT_.DBF

已選擇10行。

 

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