Oracle DBA 生涯日誌: 4月 2018

Oracle single db + data guard , resize redo log

環境: 11.2.0.4

目前的 single db + data guard 的 redo log 和 standby redo log 都是50MB , 現在想要增加到 100MB

1.主庫修改 standby_file_management 為 MANUAL

show parameter standby_file_management

alter system set standby_file_management=manual;

2.對主庫刪除舊的 standby redo log , 並且直接新增 standby redo log

SQL> select group#,status from v$standby_log;

GROUP# STATUS
---------- ----------
4 UNASSIGNED
5 UNASSIGNED
6 UNASSIGNED
7 UNASSIGNED

SQL> alter database drop standby logfile group 4;

Database altered.

SQL> alter database drop standby logfile group 5;

Database altered.

SQL> alter database drop standby logfile group 6;

Database altered.

SQL> alter database drop standby logfile group 7;

Database altered.
SQL>
SQL> alter database add standby logfile group 13 ('/ORA/11.2.4/oradata/LG/redo13.log') size 100M;

Database altered.

SQL> alter database add standby logfile group 14 ('/ORA/11.2.4/oradata/LG/redo14.log') size 100M;

Database altered.

SQL> alter database add standby logfile group 15 ('/ORA/11.2.4/oradata/LG/redo15.log') size 100M;

Database altered.

SQL> alter database add standby logfile group 16 ('/ORA/11.2.4/oradata/LG/redo16.log') size 100M;

Database altered.

SQL> alter database add standby logfile group 17 ('/ORA/11.2.4/oradata/LG/redo17.log') size 100M;

Database altered.

SQL> alter database add standby logfile group 18 ('/ORA/11.2.4/oradata/LG/redo18.log') size 100M;

Database altered.

SQL>
SQL> select group#,thread#,bytes,status from v$standby_log;

GROUP# THREAD# BYTES STATUS
---------- ---------- ---------- ----------
13 0 104857600 UNASSIGNED
14 0 104857600 UNASSIGNED
15 0 104857600 UNASSIGNED
16 0 104857600 UNASSIGNED
17 0 104857600 UNASSIGNED
18 0 104857600 UNASSIGNED

6 rows selected.

SQL>
SQL>

3.對主庫新增 redo log, 再來刪除舊的 redo log

SQL> select group#,thread#,bytes,status,archived from v$log;

GROUP# THREAD# BYTES STATUS ARC
---------- ---------- ---------- ---------------- ---
1 1 52428800 CURRENT NO
2 1 52428800 INACTIVE YES
3 1 52428800 INACTIVE YES

SQL>

SQL> alter database add logfile group 8 ('/ORA/11.2.4/oradata/LG/redo08.log') size 100M reuse;

Database altered.

SQL>
SQL> alter database add logfile group 9 ('/ORA/11.2.4/oradata/LG/redo09.log') size 100M reuse;

Database altered.

SQL>
SQL> alter database add logfile group 10 ('/ORA/11.2.4/oradata/LG/redo10.log') size 100M reuse;

Database altered.

SQL>
SQL> alter database add logfile group 11 ('/ORA/11.2.4/oradata/LG/redo11.log') size 100M reuse;

Database altered.

SQL>
SQL> alter database add logfile group 12 ('/ORA/11.2.4/oradata/LG/redo12.log') size 100M reuse;

Database altered.

SQL>

SQL>

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL>

因為 group 1 狀態是 CURRENT, 使用check point 指令讓他變成 INACTIVE

SQL>
SQL> alter system checkpoint;

System altered.

SQL>
SQL> select group#,thread#,bytes,status,archived from v$log;

GROUP# THREAD# BYTES STATUS ARC
---------- ---------- ---------- ---------------- ---
1 1 52428800 INACTIVE YES
8 1 83886080 CURRENT NO
9 1 83886080 UNUSED YES
10 1 83886080 UNUSED YES
11 1 83886080 UNUSED YES
12 1 83886080 UNUSED YES

6 rows selected.

SQL>
SQL> alter database drop logfile group 1;

Database altered.

SQL>

select f.group#,l.thread#,l.bytes,l.status,f.type from v$logfile f,v$log l where f.group#=l.group#

union all

select f.group#,s.thread#,s.bytes,s.status,f.type from v$logfile f,v$standby_log s where f.group#=s.group#;

4.先在備庫處理 standby redo log

查看一下備庫狀態
SQL> select group#,thread#,bytes,status,archived from v$log;

GROUP# THREAD# BYTES STATUS ARC
---------- ---------- ---------- ---------------- ---
1 1 52428800 CLEARING YES
2 1 52428800 CLEARING YES
3 1 52428800 CLEARING YES

SQL>
SQL>
SQL> select group#,status from v$standby_log;

GROUP# STATUS
---------- ----------
4 UNASSIGNED
5 UNASSIGNED
6 UNASSIGNED
7 UNASSIGNED

SQL>

SQL>

先關掉備庫同步

SQL>
SQL> alter database recover managed standby database cancel;

Database altered.

刪除standby redo log

SQL>
SQL> alter database drop standby logfile group 4;

Database altered.

SQL> alter database drop standby logfile group 5;

Database altered.

SQL> alter database drop standby logfile group 6;

Database altered.

SQL> alter database drop standby logfile group 7;

Database altered.

SQL>
SQL>
SQL>

新增 standby redo log

SQL>
SQL> alter database add standby logfile group 13 ('/ORA/11.2.4/oradata/LG/redo13.log') size 100M;

Database altered.

SQL> alter database add standby logfile group 14 ('/ORA/11.2.4/oradata/LG/redo14.log') size 100M;

Database altered.

SQL> alter database add standby logfile group 15 ('/ORA/11.2.4/oradata/LG/redo15.log') size 100M;

Database altered.

SQL> alter database add standby logfile group 16 ('/ORA/11.2.4/oradata/LG/redo16.log') size 100M;

Database altered.

SQL> alter database add standby logfile group 17 ('/ORA/11.2.4/oradata/LG/redo17.log') size 100M;

Database altered.

SQL> alter database add standby logfile group 18 ('/ORA/11.2.4/oradata/LG/redo18.log') size 100M;

Database altered.

SQL> select group#,status from v$standby_log;

GROUP# STATUS
---------- ----------
13 UNASSIGNED
14 UNASSIGNED
15 UNASSIGNED
16 UNASSIGNED
17 UNASSIGNED
18 UNASSIGNED

6 rows selected.

SQL>

備庫新增redo log 和刪除舊有的redo log

SQL>
SQL> alter database add logfile group 8 ('/ORA/11.2.4/oradata/LG/redo08.log') size 100M reuse;

Database altered.

SQL> alter database add logfile group 9 ('/ORA/11.2.4/oradata/LG/redo09.log') size 100M reuse;

Database altered.

SQL> alter database add logfile group 10 ('/ORA/11.2.4/oradata/LG/redo10.log') size 100M reuse;

Database altered.

SQL> alter database add logfile group 11 ('/ORA/11.2.4/oradata/LG/redo11.log') size 100M reuse;

Database altered.

SQL> alter database add logfile group 12 ('/ORA/11.2.4/oradata/LG/redo12.log') size 100M reuse;

Database altered.

SQL>
SQL>
SQL> select group#,thread#,bytes,status,archived from v$log
2 ;

GROUP# THREAD# BYTES STATUS ARC
---------- ---------- ---------- ---------------- ---
1 1 52428800 CLEARING YES
2 1 52428800 CLEARING YES
3 1 52428800 UNUSED YES
8 1 104857600 UNUSED YES
9 1 104857600 UNUSED YES
10 1 104857600 UNUSED YES
11 1 104857600 CURRENT YES
12 1 104857600 UNUSED YES

8 rows selected.

group 3 status 是 UNUSED , 可以直接刪除

SQL> alter database drop logfile group 3;

Database altered.

group 1,2 是 CLEARING ,刪除會爆 ORA-01624 錯誤先執行 clear logfile

SQL> alter database drop logfile group 2;

alter database drop logfile group 2

ERROR at line 1:

ORA-01624: log 2 needed for crash recovery of instance LG (thread 1)

ORA-00312: online log 2 thread 1: '/ORA/11.2.4/oradata/LG/redo02.log'

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

SQL>

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;

Database altered.

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;

Database altered.

SQL>

SQL> select group#,thread#,bytes,status,archived from v$log;

GROUP# THREAD# BYTES STATUS ARC

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

1 1 52428800 UNUSED YES

2 1 52428800 UNUSED YES

8 1 104857600 UNUSED YES

9 1 104857600 UNUSED YES

10 1 104857600 UNUSED YES

11 1 104857600 CURRENT YES

12 1 104857600 UNUSED YES

7 rows selected.

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL>

接下來正備庫執行

SQL>

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';

System altered.

SQL>

備庫恢復同步

SQL>

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Database altered.

正庫多次執行 alter system switch lofile;

看看 log 是否可以正常切換

RAC

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 4 ('+DATA_TAB01/lg/onlinelog/') SIZE 1000M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 group 5 ('+DATA_TAB01/lg/onlinelog/') SIZE 1000M;

alter database add logfile THREAD 1 group 8 ('/ORA/11.2.4/oradata/LG/redo08.log') size 100M reuse;
alter database add logfile THREAD 2 group 9 ('/ORA/11.2.4/oradata/LG/redo08.log') size 100M reuse;

Oracle DBA 生涯日誌

2018年4月9日星期一

ORACLE SINGLE DB + DATA GUARD RESIZE REDO LOG

文章列表

搜尋此網誌

2018年4月9日 星期一

ORACLE SINGLE DB + DATA GUARD RESIZE REDO LOG

文章列表

搜尋此網誌

2018年4月9日星期一