1)查看保护模式,一般默认最高性能
SQL> select protection_mode,protection_level from v$database;
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
如果是Maximum Protection mode需要先降级
降级
备库:
alter database recover managed standby database cancel;
主库:
alter database set standby database to maximize PERFORMANCE;
备库:
alter database recover managed standby database using current logfile disconnect from session;
SQL> select status,instance_name,database_role from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE
------------ ---------------- ----------------
OPEN jinan PRIMARY
2)查询日志大小和数量
primary
SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$log group by group#;
GROUP# Size in MB
---------- ----------
1 50
2 50
3 50
SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;
no rows selected
主机未定义standby 如果定义可按照备库方式
备库
SQL> select status,instance_name,database_role from v$database,v$Instance;
STATUS INSTANCE_NAME DATABASE_ROLE
------------ ---------------- ----------------
OPEN qingdao PHYSICAL STANDBY
SQL> select group#, sum(bytes/1024/1024)"Size in MB" from v$log group by group#;
GROUP# Size in MB
---------- ----------
1 50
2 50
3 50
SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;
GROUP# Size in MB
---------- ----------
6 50
4 50
5 50
7 50
3)修改备库参数
查看standby file 参数是auto,修改为manual
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO
SQL> alter system set standby_file_management=manual;
System altered.
SQL> alter database recover managed standby database cancel;
Database altered.
4)修改主库online日志
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 CURRENT
3 INACTIVE
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/oradata/jinan/redo03.log
/oradata/jinan/redo02.log
/oradata/jinan/redo01.log
group 1、3是inactive 可以drop ;2 是current 需要切换后变为inactive 再drop
SQL> alter database drop logfile group 1;
Database altered.
#alter database add logfile group 1 size 60M;
SQL> ALTER DATABASE ADD LOGFILE GROUP 1 ('/oradata/jinan/redo001.log') SIZE 60M;
Database altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 UNUSED
2 CURRENT
3 INACTIVE
SQL> alter database drop logfile group 3;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE GROUP 3 ('/oradata/jinan/redo003.log') SIZE 60M;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 ACTIVE
3 UNUSED
SQL> alter system archive log current;
System altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 ACTIVE
2 CURRENT
3 ACTIVE
SQL> alter system checkpoint;
System altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE
3 INACTIVE
SQL> alter database drop logfile group 2;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE GROUP 2 ('/oradata/jinan/redo002.log') SIZE 60M;
Database altered.
SQL> select group#, sum(bytes/1024/1024)"Size in MB" from v$log group by group#;
GROUP# Size in MB
---------- ----------
1 60
2 60
3 60
5)主库的standby redo(由于实验环境未配置,若配置也是直接drop 再add 可参照备库standby redo的方法)
SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;
GROUP# size in MB
---------- ----------
6 50
4 50
5 50
7 50
SQL> select group#,status from v$standby_log;
GROUP# STATUS
---------- ----------
4 UNASSIGNED
5 UNASSIGNED
6 UNASSIGNED
7 UNASSIGNED
以下是示例 mos上的示例
==================================================================
The status of the Standby Redo Logs (SRL) on the Primary database would be UNASSIGNED as they would be used only when the primary database starts behaving as a Standby (Switchover)
We can easily drop the UNASSIGNED SRLs and re-create them with the new size.
SQL> alter database drop standby logfile group 4;
Database altered.
SQL> alter database add standby logfile group 4 size 100M;
Database altered.
Repeat this for the remaining SRLs on the primary database and we can see below that all the SRL on the Primary database have been resized.
SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;
GROUP# size in MB
---------- ----------
6 100
4 100
5 100
7 100
=====================================================================
6)备库online redo
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/data/qingdao/redo03.log
/data/qingdao/redo01.log
/data/qingdao/stdby_redo4.log
/data/qingdao/stdby_redo5.log
/data/qingdao/stdby_redo6.log
/data/qingdao/stdby_redo7.log
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 CLEARING
3 CLEARING
SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files
SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files
不管是current 还是clearing 直接drop都是报错的,需要取消应用
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database clear logfile group 2;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE GROUP 2 ('/data/qingdao/redo002.log') SIZE 60M;
Database altered.
SQL> alter database clear logfile group 3;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE GROUP 3 ('/data/qingdao/redo003.log') SIZE 60M;
Database altered.
主库切日志SQL> alter system switch logfile;
System altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CLEARING
2 CURRENT
3 UNUSED
SQL> alter database clear logfile group 1;
Database altered.
SQL> alter database drop logfile group 1;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE GROUP 1 ('/data/qingdao/redo001.log') SIZE 60M;
Database altered.
SQL> SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$log group by group#;
GROUP# size in MB
---------- ----------
1 60
2 60
3 60
7)备库standby redo
SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;
GROUP# size in MB
---------- ----------
6 50
4 50
5 50
7 50
都是unassigned,直接drop后add
SQL> select group#,status from v$standby_log;
GROUP# STATUS
---------- ----------
4 UNASSIGNED
5 UNASSIGNED
6 UNASSIGNED
7 UNASSIGNED
Clear the SRL group whose status is ACTIVE as done earlier, drop the group and re-create
them with the size same as that of the Online Redo Logs.
SQL> alter database drop standby logfile group 4;
Database altered
SQL> alter database add standby logfile group 4 ('/data/qingdao/stdby_redo04.log') size 60m;
Database altered.
SQL> alter database drop standby logfile group 5;
Database altered
SQL> alter database add standby logfile group 5 ('/data/qingdao/stdby_redo05.log') size 60m;
Database altered.
SQL> alter database drop standby logfile group 6;
Database altered
SQL> alter database add standby logfile group 6 ('/data/qingdao/stdby_redo06.log') size 60m;
Database altered.
SQL> alter database drop standby logfile group 7;
Database altered
SQL> alter database add standby logfile group 7 ('/data/qingdao/stdby_redo07.log') size 60m;
Database altered.
SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;
GROUP# size in MB
---------- ----------
6 60
4 60
5 60
7 60
SQL> alter system set standby_file_management=auto;
System altered.
SQL> alter database recover managed standby database disconnect from session using current logfile;
Database altered.
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CLOSING 24
ARCH CLOSING 23
ARCH CONNECTED 0
ARCH CLOSING 29
RFS IDLE 0
RFS IDLE 0
RFS IDLE 32
RFS IDLE 0
MRP0 WAIT_FOR_LOG 32
9 rows selected.
8)检查
主库
SQL>select max(sequence#) from v$archived_log;
备库
SQL>select max(sequence#) from v$archived_log where applied='YES';