通過修改log_archive_dest_state_n引數的狀態來控制主庫的日誌檔案傳輸

xulongxc發表於2015-09-24

Attribute	Description
`ENABLE`	Log transport services can transmit redo data to this destination. `ENABLE` is the default.
`DEFER`	Log transport services will not transmit redo data to this destination. This is a valid but unused destination.
`ALTERNATE`	This destination is not enabled, but it will become enabled if communication to its associated destination fails.
`RESET`	Functions the same as `DEFER`, but clears any error messages for the destination if it had previously failed.

SQL> alter system set log_archive_dest_state_2="defer";

System altered.

SQL> show parameter dest

NAME TYPE VALUE

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

audit_file_dest string /u01/app/oracle/admin/tbtest/a

dump

background_dump_dest string /u01/app/oracle/diag/rdbms/pr_

tbtest/tbtest/trace

core_dump_dest string /u01/app/oracle/diag/rdbms/pr_

tbtest/tbtest/cdump

cursor_bind_capture_destination string memory+disk

db_create_file_dest string

db_create_online_log_dest_1 string

db_create_online_log_dest_2 string

db_create_online_log_dest_3 string

NAME TYPE VALUE

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

db_create_online_log_dest_4 string

db_create_online_log_dest_5 string

db_recovery_file_dest string /u01/app/oracle/fast_recovery_

area

db_recovery_file_dest_size big integer 4122M

diagnostic_dest string /u01/app/oracle

log_archive_dest string

log_archive_dest_1 string location=/u01/archlog valid_

for=(all_logfiles,all_roles)

db_unique_name=pr_tbtest

log_archive_dest_10 string

NAME TYPE VALUE

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

log_archive_dest_11 string

log_archive_dest_12 string

log_archive_dest_13 string

log_archive_dest_14 string

log_archive_dest_15 string

log_archive_dest_16 string

log_archive_dest_17 string

log_archive_dest_18 string

log_archive_dest_19 string

log_archive_dest_2 string service=st_tbtest lgwr async

valid_for=(online_logfiles,pri

NAME TYPE VALUE

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

mary_role) db_unique_name=st_

tbtest

log_archive_dest_20 string

log_archive_dest_21 string

log_archive_dest_22 string

log_archive_dest_23 string

log_archive_dest_24 string

log_archive_dest_25 string

log_archive_dest_26 string

log_archive_dest_27 string

log_archive_dest_28 string

NAME TYPE VALUE

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

log_archive_dest_29 string

log_archive_dest_3 string

log_archive_dest_30 string

log_archive_dest_31 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

log_archive_dest_9 string

log_archive_dest_state_1 string enable

NAME TYPE VALUE

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

log_archive_dest_state_10 string enable

log_archive_dest_state_11 string enable

log_archive_dest_state_12 string enable

log_archive_dest_state_13 string enable

log_archive_dest_state_14 string enable

log_archive_dest_state_15 string enable

log_archive_dest_state_16 string enable

log_archive_dest_state_17 string enable

log_archive_dest_state_18 string enable

log_archive_dest_state_19 string enable

log_archive_dest_state_2 string defer

NAME TYPE VALUE

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

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

log_archive_dest_state_3 string DEFER

NAME TYPE VALUE

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

log_archive_dest_state_30 string enable

log_archive_dest_state_31 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

log_archive_duplex_dest string

log_archive_min_succeed_dest integer 1

standby_archive_dest string ?/dbs/arch

NAME TYPE VALUE

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

user_dump_dest string /u01/app/oracle/diag/rdbms/pr_

tbtest/tbtest/trace

SQL> select STATUS from v$archive_dest;

STATUS

---------

VALID

DEFERRED

INACTIVE

STATUS

---------

INACTIVE

STATUS

---------

INACTIVE

31 rows selected.

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

PID PROCESS STATUS SEQUENCE#

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

4357 ARCH CLOSING 615

4359 ARCH CLOSING 618

4361 ARCH CLOSING 298

4363 ARCH CLOSING 619

4365 LNS WRITING 620

SQL>

SQL> drop table test2;

Table dropped.

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

PID PROCESS STATUS SEQUENCE#

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

4357 ARCH CLOSING 615

4359 ARCH CLOSING 618

4361 ARCH CLOSING 298

4363 ARCH CLOSING 619

4365 LNS WRITING 620

SQL> create table test3 as select * from dba_objects;

Table created.

SQL> select count(*) from test3;

COUNT(*)

----------

74863

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

PID PROCESS STATUS SEQUENCE#

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

4357 ARCH CLOSING 615

4359 ARCH CLOSING 620

4361 ARCH CLOSING 298

4363 ARCH CLOSING 619

4365 LNS CLOSING 620

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

PID PROCESS STATUS SEQUENCE#

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

4357 ARCH CLOSING 615

4359 ARCH CLOSING 620

4361 ARCH CLOSING 298

4363 ARCH CLOSING 619

4365 LNS CLOSING 620

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

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

PID PROCESS STATUS SEQUENCE#

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

4357 ARCH CLOSING 622

4359 ARCH CLOSING 620

4361 ARCH CLOSING 298

4363 ARCH CLOSING 621

4365 LNS CLOSING 620

SQL> /

PID PROCESS STATUS SEQUENCE#

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

4357 ARCH CLOSING 622

4359 ARCH CLOSING 620

4361 ARCH CLOSING 298

4363 ARCH CLOSING 621

4365 LNS CLOSING 620

SQL> /

PID PROCESS STATUS SEQUENCE#

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

4357 ARCH CLOSING 622

4359 ARCH CLOSING 620

4361 ARCH CLOSING 298

4363 ARCH CLOSING 621

4365 LNS CLOSING 620

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> 26157 ARCH CLOSING 618

26159 ARCH CLOSING 620

26161 ARCH CONNECTED 0

SQL> SQL> SQL> 26163 ARCH CLOSING 619

SQL> 26458 MRP0 WAIT_FOR_LOG 621

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

PID PROCESS STATUS SEQUENCE#

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

4357 ARCH CLOSING 625

4359 ARCH CLOSING 623

4361 ARCH CLOSING 298

4363 ARCH CLOSING 624

4365 LNS CLOSING 620

SQL>

SQL> alter system set log_archive_dest_state_2="enable";

System altered.

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

PID PROCESS STATUS SEQUENCE#

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

4357 ARCH OPENING 626

4359 ARCH CLOSING 623

4361 ARCH CLOSING 298

4363 ARCH CLOSING 626

4365 LNS OPENING 627

SQL>

SQL> drop table test3;

Table dropped.

SQL>

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

Oracle 11g Data Guard暫停物理備庫的日誌傳輸(log_archive_dest_state_n的defer引數)
2015-12-01
OracleHive
16、重做日誌檔案的狀態及重做日誌組的狀態說明
2007-08-03
透過alert日誌重建引數檔案
2015-05-07
Oracle 11g 通過 RMAN 檢查資料檔案、控制檔案、引數檔案的狀態和是否存在壞塊
2015-12-10
Oracle
【DataGuard】Oracle DataGuard 日誌傳輸狀態監控
2015-10-15
Oracle
通過配置檔案來修改WAS控制檯Session過期時間的方法
2010-06-17
Session
監聽狀態對dataguard及其日誌傳輸的影響
2017-03-13
資料庫引數檔案控制檔案日誌檔案資料檔案跟蹤檔案等8大檔案的字典
2016-12-22
資料庫
談談Oracle日誌檔案的INVALID狀態
2017-02-21
Oracle
通過反射獲取上傳檔案方法引數中的檔名
2022-01-11
反射
MySQL資料庫中的日誌檔案---(4)配置日誌檔案輸出路徑
2011-10-10
MySql資料庫
mysql的日誌引數修改的問題.
2008-07-07
MySql
通過Xtrabackup日誌來恢復檢查點檔案
2017-12-18
日誌的狀態
2010-04-13
修改pfile引數檔案過程的異常
2016-10-15
談談Oracle日誌檔案的INVALID狀態（上）
2012-09-10
Oracle
談談Oracle日誌檔案的INVALID狀態（下）
2012-09-11
Oracle
【DataGuard】10GR 日誌傳輸服務引數
2011-09-04
通過RMAN設定standby接收日誌後主庫歸檔日誌才可刪除
2020-05-13
SpringBoot通過yml和xml檔案配置日誌輸出
2019-04-01
Spring BootXML
ORACLE中沒有引數檔案和控制檔案如何通過rman恢復資料庫
2015-02-04
Oracle資料庫
重新建立控制檔案，修改MAXDATAFILES引數
2012-01-20
MySQL 修改InnoDB重做日誌檔案的數量或大小
2022-03-17
MySql
線上修改重做日誌檔案的大小
2015-02-27
修改Oracle重做日誌檔案的大小
2015-05-17
Oracle
inactive狀態日誌組檔案損壞的恢復
2015-07-04
控制檔案/歸檔日誌
2009-03-12
window下刪除過舊的日誌檔案指令碼(如jboss的日誌檔案資料庫的trace檔案)
2011-11-14
指令碼資料庫
增加日誌檔案，修改當前日誌檔案大小
2011-03-30
C++中的檔案輸入/輸出（4）：檢測輸入/輸出的狀態標誌 (轉)
2008-01-11
C++
【備份恢復】恢復inactive狀態的日誌檔案
2016-10-16
資料庫易混淆引數名以及引數檔案啟動資料庫到nomount狀態
2014-01-21
資料庫
如何在程式執行時動態修改它的引數(狀態)？
2016-10-13
ABAP 通過SQL修改某個程式的鎖定狀態
2010-01-21
SQL
建立新的控制檔案，更改引數
2016-10-24
如何通過trn日誌檔案恢復SQL Server
2010-04-09
SQLServer
邏輯dg刪除主庫過期歸檔日誌
2014-05-18
通過Docker部署Java專案的日誌輸出到宿主機指定目錄
2021-12-16
DockerJava

通過修改log_archive_dest_state_n引數的狀態來控制主庫的日誌檔案傳輸

相關文章