查询日志文件
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/node/redo01.log
2 ONLINE /u01/app/oracle/oradata/node/redo02.log
3 ONLINE /u01/app/oracle/oradata/node/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/node/redo01.log
创建日志组1
SQL> alter database add logfile group 1
('/u01/app/oracle/oradata/node/redo01a.log','/u01/app/oracle/oradata/node/redo01b.log') size 50M;
Database altered.
删除日志组2
SQL> alter database drop logfile group 2;
Database altered.
SQL> host rm -rf /u01/app/oracle/oradata/node/redo02.log
创建日志组2
SQL> alter database add logfile group 2
('/u01/app/oracle/oradata/node/redo02a.log','/u01/app/oracle/oradata/node/redo02b.log') size 50M;
Database altered.
日志组切换
SQL> alter system switch logfile;
System altered.
执行检查点
SQL> alter system checkpoint;
System altered.
删除日志组3
SQL> alter database drop logfile group 3;
Database altered.
SQL> host rm -rf /u01/app/oracle/oradata/node/redo03.log
创建日志组3
SQL> alter database add logfile group 3
('/u01/app/oracle/oradata/node/redo03a.log','/u01/app/oracle/oradata/node/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/node/redo01a.log
1 ONLINE /u01/app/oracle/oradata/node/redo01b.log
2 ONLINE /u01/app/oracle/oradata/node/redo02b.log
2 ONLINE /u01/app/oracle/oradata/node/redo02a.log
3 ONLINE /u01/app/oracle/oradata/node/redo03b.log
3 ONLINE /u01/app/oracle/oradata/node/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/node/redo01c.log' to group 1,
'/u01/app/oracle/oradata/node/redo02c.log' to group 2,
'/u01/app/oracle/oradata/node/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/node/redo01b.log
1 INVALID ONLINE /u01/app/oracle/oradata/node/redo01c.log
1 ONLINE /u01/app/oracle/oradata/node/redo01a.log
2 ONLINE /u01/app/oracle/oradata/node/redo02a.log
2 ONLINE /u01/app/oracle/oradata/node/redo02b.log
2 INVALID ONLINE /u01/app/oracle/oradata/node/redo02c.log
3 ONLINE /u01/app/oracle/oradata/node/redo03a.log
3 ONLINE /u01/app/oracle/oradata/node/redo03b.log
3 INVALID ONLINE /u01/app/oracle/oradata/node/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/node/redo03c.log';
Database altered.
SQL> host rm -rf /u01/app/oracle/oradata/node/redo03c.log
SQL> alter database drop logfile member
'/u01/app/oracle/oradata/node/redo02c.log';
Database altered.
SQL> host rm -rf /u01/app/oracle/oradata/node/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/node/redo01c.log';
Database altered.
SQL> host rm -rf /u01/app/oracle/oradata/node/redo01c.log
移动日志文件
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> quit
[oracle@node01 ~]$ cp /u01/app/oracle/oradata/node/redo01b.log /u02/app/oracle/oradata/node/redo01b.log
[oracle@node01 ~]$ cp /u01/app/oracle/oradata/node/redo02b.log /u02/app/oracle/oradata/node/redo02b.log
[oracle@node01 ~]$ cp /u01/app/oracle/oradata/node/redo03b.log /u02/app/oracle/oradata/node/redo03b.log
[oracle@node01 ~]$ 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/node/redo01b.log'
to '/u02/app/oracle/oradata/node/redo01b.log';
Database altered.
SQL> alter database
rename file '/u01/app/oracle/oradata/node/redo02b.log'
to '/u02/app/oracle/oradata/node/redo02b.log';
Database altered.
SQL> alter database
rename file '/u01/app/oracle/oradata/node/redo03b.log'
to '/u02/app/oracle/oradata/node/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/node/redo01a.log
1 ONLINE /u02/app/oracle/oradata/node/redo01b.log
2 ONLINE /u02/app/oracle/oradata/node/redo02b.log
2 ONLINE /u01/app/oracle/oradata/node/redo02a.log
3 ONLINE /u02/app/oracle/oradata/node/redo03b.log
3 ONLINE /u01/app/oracle/oradata/node/redo03a.log
SQL> select dbid,name,log_mode from v$database;
DBID NAME LOG_MODE
---------- -------------------------------------------------- ------------
1252218683 CORE ARCHIVELOG
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 30
Next log sequence to archive 32
Current log sequence 32
SQL> show parameter log_archive_dest_1;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string location=USE_DB_RECOVERY_FILE_
DEST valid_for=(all_logfiles,a
ll_roles) db_unique_name=coremdb
SQL> show parameter log_archive_dest_2;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service=coresdb lgwr async val
id_for=(online_logfiles,primar
y_role) db_unique_name=coresdb
SQL> show parameter log_archive_min_succeed_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_min_succeed_dest integer 1
SQL> show parameter log_archive_max
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_max_processes integer 4
归档目的地
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 OPTIONAL USE_DB_RECOVERY_FILE_DEST
LOG_ARCHIVE_DEST_2 VALID OPTIONAL coresdb
LOG_ARCHIVE_DEST_3 INACTIVE OPTIONAL
LOG_ARCHIVE_DEST_4 INACTIVE OPTIONAL
......
归档进程
SQL> select * from v$archive_processes;
PROCESS STATUS LOG_SEQUENCE STAT ROLES
---------- ---------- ------------ ---- ------------------------------------
0 ACTIVE 30 IDLE
1 ACTIVE 0 IDLE NO_FAL NO_SRL
2 ACTIVE 0 IDLE HEART_BEAT
3 ACTIVE 0 IDLE
4 STOPPED 0 IDLE
5 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 NAME SEQUENCE# STATUS
---------- -------------------------------------------------- ---------- ----------
1 27 D
1 /u01/app/oracle/fast_recovery_area/COREMDB/archive 28 A
log/2023_08_22/o1_mf_1_28_lg8pwsrd_.arc
1 /u01/app/oracle/fast_recovery_area/COREMDB/archive 29 A
log/2023_08_22/o1_mf_1_29_lg8pzzbm_.arc
1 /u01/app/oracle/fast_recovery_area/COREMDB/archive 30 A
log/2023_08_22/o1_mf_1_30_lg8tln4m_.arc
2 coresdb 30 A
DEST_ID NAME SEQUENCE# STATUS
---------- -------------------------------------------------- ---------- ----------
1 /u01/app/oracle/fast_recovery_area/COREMDB/archive 31 A
log/2023_08_22/o1_mf_1_31_lg8tlv2z_.arc
2 coresdb 31 A
