【Oracle Database】數(shù)據(jù)庫日志管理

發(fā)布時間：2020-07-29 11:57:06 來源：網絡閱讀：1205 作者：NOGYMS 欄目：關系型數(shù)據(jù)庫

查詢日志文件
SQL>?col?member?for?a50
SQL>?select?group#,status,type,member?from?v$logfile?order?by?group#;

????GROUP#?STATUS??TYPE????MEMBER
----------?-------?-------?--------------------------------------------------
?????????1?????????ONLINE??/u01/app/oracle/oradata/wallet/redo01.log
?????????2?????????ONLINE??/u01/app/oracle/oradata/wallet/redo02.log
?????????3?????????ONLINE??/u01/app/oracle/oradata/wallet/redo03.log

查詢日志組
SQL>?select?group#,sequence#,members,bytes,status,archived?from?v$log;

????GROUP#??SEQUENCE#????MEMBERS??????BYTES?STATUS???????????ARC
----------?----------?----------?----------?----------------?---
?????????1?????????25??????????1???52428800?INACTIVE?????????YES
?????????2?????????26??????????1???52428800?INACTIVE?????????YES
?????????3?????????27??????????1???52428800?CURRENT??????????NO

刪除日志組1
SQL>?alter?database?drop?logfile?group?1;
Database?altered.

SQL>?host?rm?-rf?/u01/app/oracle/oradata/wallet/redo01.log

創(chuàng)建日志組1
SQL>?alter?database?add?logfile?group?1
('/u01/app/oracle/oradata/wallet/redo01a.log','/u01/app/oracle/oradata/wallet/redo01b.log')
size?50M;
Database?altered.

刪除日志組2
SQL>?alter?database?drop?logfile?group?2;
Database?altered.

SQL>?host?rm?-rf?/u01/app/oracle/oradata/wallet/redo02.log

創(chuàng)建日志組2
SQL>?alter?database?add?logfile?group?2
('/u01/app/oracle/oradata/wallet/redo02a.log','/u01/app/oracle/oradata/wallet/redo02b.log')
size?50M;
Database?altered.

日志組切換
SQL>?alter?system?switch?logfile;
System?altered.

執(zhí)行檢查點
SQL>?alter?system?checkpoint;
System?altered.

刪除日志組3
SQL>?alter?database?drop?logfile?group?3;
Database?altered.

SQL>?host?rm?-rf?/u01/app/oracle/oradata/wallet/redo03.log

創(chuàng)建日志組3
SQL>?alter?database?add?logfile?group?3
('/u01/app/oracle/oradata/wallet/redo03a.log','/u01/app/oracle/oradata/wallet/redo03b.log')
size?50M;
Database?altered.

SQL>?select?group#,status,type,member?from?v$logfile?order?by?group#;

????GROUP#?STATUS??TYPE????MEMBER
----------?-------?-------?--------------------------------------------------
?????????1?????????ONLINE??/u01/app/oracle/oradata/wallet/redo01a.log
?????????1?????????ONLINE??/u01/app/oracle/oradata/wallet/redo01b.log
?????????2?????????ONLINE??/u01/app/oracle/oradata/wallet/redo02b.log
?????????2?????????ONLINE??/u01/app/oracle/oradata/wallet/redo02a.log
?????????3?????????ONLINE??/u01/app/oracle/oradata/wallet/redo03b.log
?????????3?????????ONLINE??/u01/app/oracle/oradata/wallet/redo03a.log
?????????
SQL>?select?group#,sequence#,members,bytes,status,archived?from?v$log;

????GROUP#??SEQUENCE#????MEMBERS??????BYTES?STATUS???????????ARC
----------?----------?----------?----------?----------------?---
?????????1?????????28??????????2???52428800?CURRENT??????????NO
?????????2??????????0??????????2???52428800?UNUSED???????????YES
?????????3??????????0??????????2???52428800?UNUSED???????????YES


增加日志文件
SQL>??alter?database?add?logfile?member
'/u01/app/oracle/oradata/wallet/redo01c.log'?to?group?1,
'/u01/app/oracle/oradata/wallet/redo02c.log'?to?group?2,
'/u01/app/oracle/oradata/wallet/redo03c.log'?to?group?3;
Database?altered.

SQL>?select?group#,status,type,member?from?v$logfile?order?by?group#;

????GROUP#?STATUS??TYPE????MEMBER
----------?-------?-------?--------------------------------------------------
?????????1?????????ONLINE??/u01/app/oracle/oradata/wallet/redo01b.log
?????????1?INVALID?ONLINE??/u01/app/oracle/oradata/wallet/redo01c.log
?????????1?????????ONLINE??/u01/app/oracle/oradata/wallet/redo01a.log
?????????2?????????ONLINE??/u01/app/oracle/oradata/wallet/redo02a.log
?????????2?????????ONLINE??/u01/app/oracle/oradata/wallet/redo02b.log
?????????2?INVALID?ONLINE??/u01/app/oracle/oradata/wallet/redo02c.log
?????????3?????????ONLINE??/u01/app/oracle/oradata/wallet/redo03a.log
?????????3?????????ONLINE??/u01/app/oracle/oradata/wallet/redo03b.log
?????????3?INVALID?ONLINE??/u01/app/oracle/oradata/wallet/redo03c.log

SQL>?select?group#,sequence#,members,bytes,status,archived?from?v$log;???????

????GROUP#??SEQUENCE#????MEMBERS??????BYTES?STATUS???????????ARC
----------?----------?----------?----------?----------------?---
?????????1?????????28??????????3???52428800?CURRENT??????????NO
?????????2??????????0??????????3???52428800?UNUSED???????????YES
?????????3??????????0??????????3???52428800?UNUSED???????????YES
?????????
刪除日志文件
SQL>?alter?database?drop?logfile?member
'/u01/app/oracle/oradata/wallet/redo03c.log';
Database?altered.

SQL>?host?rm?-rf?/u01/app/oracle/oradata/wallet/redo03c.log

SQL>??alter?database?drop?logfile?member
'/u01/app/oracle/oradata/wallet/redo02c.log';
Database?altered.

SQL>?host?rm?-rf?/u01/app/oracle/oradata/wallet/redo02c.log


SQL>?alter?system?switch?logfile;

System?altered.

SQL>?alter?system?checkpoint;
System?altered.

SQL>??alter?database?drop?logfile?member
'/u01/app/oracle/oradata/wallet/redo01c.log';
Database?altered.

SQL>?host?rm?-rf?/u01/app/oracle/oradata/wallet/redo01c.log

移動日志文件
SQL>?shutdown?immediate
Database?closed.
Database?dismounted.
ORACLE?instance?shut?down.
SQL>?quit

[oracle@wallet01?~]$?cp?/u01/app/oracle/oradata/wallet/redo01b.log?/u02/app/oracle/oradata/wallet/redo01b.log
[oracle@wallet01?~]$?cp?/u01/app/oracle/oradata/wallet/redo02b.log?/u02/app/oracle/oradata/wallet/redo02b.log?
[oracle@wallet01?~]$?cp?/u01/app/oracle/oradata/wallet/redo03b.log?/u02/app/oracle/oradata/wallet/redo03b.log??

[oracle@wallet01?~]$?sqlplus?/?as?sysdba
SQL>?startup?mount
ORACLE?instance?started.

Total?System?Global?Area?1068937216?bytes
Fixed?Size??????????????????2260088?bytes
Variable?Size?????????????335545224?bytes
Database?Buffers??????????725614592?bytes
Redo?Buffers????????????????5517312?bytes
Database?mounted.

SQL>?alter?database
rename?file?'/u01/app/oracle/oradata/wallet/redo01b.log'
to?'/u02/app/oracle/oradata/wallet/redo01b.log';
Database?altered.

SQL>?alter?database
rename?file?'/u01/app/oracle/oradata/wallet/redo02b.log'
to?'/u02/app/oracle/oradata/wallet/redo02b.log';
Database?altered.

SQL>?alter?database
rename?file?'/u01/app/oracle/oradata/wallet/redo03b.log'
to?'/u02/app/oracle/oradata/wallet/redo03b.log';
Database?altered.

SQL>?alter?database?open;
Database?altered.

SQL>?select?group#,status,type,member?from?v$logfile?order?by?group#;

????GROUP#?STATUS??TYPE????MEMBER
----------?-------?-------?--------------------------------------------------
?????????1?????????ONLINE??/u01/app/oracle/oradata/wallet/redo01a.log
?????????1?????????ONLINE??/u02/app/oracle/oradata/wallet/redo01b.log
?????????2?????????ONLINE??/u02/app/oracle/oradata/wallet/redo02b.log
?????????2?????????ONLINE??/u01/app/oracle/oradata/wallet/redo02a.log
?????????3?????????ONLINE??/u02/app/oracle/oradata/wallet/redo03b.log
?????????3?????????ONLINE??/u01/app/oracle/oradata/wallet/redo03a.log

SQL>?select?dbid,name,log_mode?from?v$database;

??????DBID?NAME??????LOG_MODE
----------?---------?------------
3215665862?WALLET????NOARCHIVELOG

SQL>?archive?log?list;
Database?log?mode??????????????No?Archive?Mode
Automatic?archival?????????????Disabled
Archive?destination?????????????/dbs/arch
Oldest?online?log?sequence?????28
Current?log?sequence???????????29

SQL>?shutdown?immediate
Database?closed.
Database?dismounted.
ORACLE?instance?shut?down.

SQL>?startup?mount
ORACLE?instance?started.

Total?System?Global?Area?1068937216?bytes
Fixed?Size??????????????????2260088?bytes
Variable?Size?????????????335545224?bytes
Database?Buffers??????????725614592?bytes
Redo?Buffers????????????????5517312?bytes
Database?mounted.

SQL>?alter?system?set?log_archive_dest_1='LOCATION=/u01/app/oracle/archive01/?mandatory';
System?altered.

SQL>?alter?system?set?log_archive_dest_2='LOCATION=/u02/app/oracle/archive02/?optional';
System?altered.

SQL>?alter?database?archivelog;
Database?altered.

SQL>?alter?database?open;
Database?altered.

SQL>?select?dbid,name,log_mode?from?v$database;

??????DBID?NAME??????LOG_MODE
----------?---------?------------
3215665862?WALLET????ARCHIVELOG

SQL>?archive?log?list;
Database?log?mode??????????????Archive?Mode
Automatic?archival?????????????Enabled
Archive?destination????????????/u02/app/oracle/archive02/
Oldest?online?log?sequence?????28
Next?log?sequence?to?archive???29
Current?log?sequence???????????29

SQL>?show?parameter?log_archive_dest_1;
NAME?????????????????????????????????TYPE??????????????????????????????VALUE
------------------------------------?---------------------------------?------------------------------
log_archive_dest_1???????????????????string????????????????????????????LOCATION=/u01/app/oracle/archi
???????????????????????????????????????????????????????????????????????ve01/?mandatory???????????????????????????????????????????????????????????????????????
SQL>?show?parameter?log_archive_dest_2;
NAME?????????????????????????????????TYPE??????????????????????????????VALUE
------------------------------------?---------------------------------?------------------------------
log_archive_dest_2???????????????????string????????????????????????????LOCATION=/u02/app/oracle/archi
???????????????????????????????????????????????????????????????????????ve02/?optional
SQL>?show?parameter?log_archive_max
NAME?????????????????????????????????TYPE??????????????????????????????VALUE
------------------------------------?---------------------------------?------------------------------
log_archive_max_processes????????????integer???????????????????????????4

SQL>?show?parameter?log_archive_min_succeed_dest
NAME?????????????????????????????????TYPE??????????????????????????????VALUE
------------------------------------?---------------------------------?------------------------------
log_archive_min_succeed_dest?????????integer???????????????????????????1

SQL>?show?parameter?log_archive_format
NAME?????????????????????????????????TYPE??????????????????????????????VALUE
------------------------------------?---------------------------------?------------------------------
log_archive_format???????????????????string????????????????????????????%t_%s_%r.dbf

手動歸檔日志文件
SQL>?alter?system?archive?log?current;
System?altered.

歸檔目的地
SQL>?set?line?200
SQL>?col?dest_name?for?a30
SQL>?col?destination?for?a30
SQL>?select?dest_name,status,binding,destination?from?v$archive_dest;
DEST_NAME??????????????????????STATUS??????????????????????BINDING?????????????????????DESTINATION
------------------------------?---------------------------?---------------------------?------------------------------
LOG_ARCHIVE_DEST_1?????????????VALID???????????????????????MANDATORY???????????????????/u01/app/oracle/archive01/
LOG_ARCHIVE_DEST_2?????????????VALID???????????????????????OPTIONAL????????????????????/u02/app/oracle/archive02/
LOG_ARCHIVE_DEST_3?????????????INACTIVE????????????????????OPTIONAL
LOG_ARCHIVE_DEST_4?????????????INACTIVE????????????????????OPTIONAL
LOG_ARCHIVE_DEST_5?????????????INACTIVE????????????????????OPTIONAL
LOG_ARCHIVE_DEST_6?????????????INACTIVE????????????????????OPTIONAL
LOG_ARCHIVE_DEST_7?????????????INACTIVE????????????????????OPTIONAL
LOG_ARCHIVE_DEST_8?????????????INACTIVE????????????????????OPTIONAL
LOG_ARCHIVE_DEST_9?????????????INACTIVE????????????????????OPTIONAL
LOG_ARCHIVE_DEST_10????????????INACTIVE????????????????????OPTIONAL
LOG_ARCHIVE_DEST_11????????????INACTIVE????????????????????OPTIONAL

歸檔進程
SQL>?select?*?from?v$archive_processes;
???PROCESS?STATUS?????????????????????????LOG_SEQUENCE?STATE
----------?------------------------------?------------?------------
?????????0?ACTIVE????????????????????????????????????0?IDLE
?????????1?ACTIVE????????????????????????????????????0?IDLE
?????????2?ACTIVE????????????????????????????????????0?IDLE
?????????3?ACTIVE????????????????????????????????????0?IDLE
?????????4?STOPPED???????????????????????????????????0?IDLE
?????????5?STOPPED???????????????????????????????????0?IDLE
?????????6?STOPPED???????????????????????????????????0?IDLE
?????????7?STOPPED???????????????????????????????????0?IDLE
?????????8?STOPPED???????????????????????????????????0?IDLE
?????????9?STOPPED???????????????????????????????????0?IDLE
????????10?STOPPED???????????????????????????????????0?IDLE
????????
歸檔日志文件
SQL>?set?line?200
SQL>?col?name?for?a50
SQL>?col?status?for?a10
SQL>?select?dest_id,name,sequence#,status?from?v$archived_log;

???DEST_ID?Tablespace?Name?????????????????????????????????????SEQUENCE#?Status
----------?--------------------------------------------------?----------?----------
?????????1?/u01/app/oracle/archive01/1_29_1007721545.dbf??????????????29?A
?????????2?/u02/app/oracle/archive02/1_29_1007721545.dbf??????????????29?A
?????????1?/u01/app/oracle/archive01/1_30_1007721545.dbf??????????????30?A
?????????2?/u02/app/oracle/archive02/1_30_1007721545.dbf??????????????30?A
?????????1?/u01/app/oracle/archive01/1_31_1007721545.dbf??????????????31?A
?????????2?/u02/app/oracle/archive02/1_31_1007721545.dbf??????????????31?A
?????????1?/u01/app/oracle/archive01/1_32_1007721545.dbf??????????????32?A
?????????2?/u02/app/oracle/archive02/1_32_1007721545.dbf??????????????32?A

向AI問一下細節(jié)

【Oracle Database】數(shù)據(jù)庫日志管理

猜你喜歡

最新資訊

相關推薦

相關標簽