备库关闭日志应用：
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
日志报错：
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 2463385381
Thu Oct 12 23:11:02 2017
MRP0: Background Media Recovery process shutdown (SCPRDDG)
Managed Standby Recovery Canceled (SCPRDDG)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
主库：
SQL> select group#,thread#,members,status,bytes/1024/1024 as size_m from v$log;
GROUP# THREAD# MEMBERS STATUS SIZE_M
---------- ---------- ---------- ---------------- ----------
1 1 1 INACTIVE 50
2 1 1 CURRENT 50
3 2 1 INACTIVE 50
4 2 1 CURRENT 50
SQL> select group#,member from v$logfile order by 1;
GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
1
+DATA/scprd/onlinelog/group_1.262.929789943
2
+DATA/scprd/onlinelog/group_2.261.929789943
3
+DATA/scprd/onlinelog/group_3.258.929790053
GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
4
+DATA/scprd/onlinelog/group_4.257.929790053
5
+DATA/scprd/onlinelog/standby05.log
6
+DATA/scprd/onlinelog/standby06.log
GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
7
+DATA/scprd/onlinelog/standby07.log
8
+DATA/scprd/onlinelog/standby08.log
9
+DATA/scprd/onlinelog/standby09.log
GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
10
+DATA/scprd/onlinelog/standby10.log
10 rows selected.
SQL> alter database add logfile thread 1 group 11 '+DATA' size 250M;
Database altered.
SQL> select group#,thread#,members,status,bytes/1024/1024 as size_m from v$log;
GROUP# THREAD# MEMBERS STATUS SIZE_M
---------- ---------- ---------- ---------------- ----------
1 1 1 INACTIVE 50
2 1 1 CURRENT 50
3 2 1 INACTIVE 50
4 2 1 CURRENT 50
11 1 1 UNUSED 250
SQL> alter database add logfile thread 2 group 15 '+DATA' size 250M;
Database altered.
SQL> select group#,thread#,members,status,bytes/1024/1024 as size_m from v$log;
GROUP# THREAD# MEMBERS STATUS SIZE_M
---------- ---------- ---------- ---------------- ----------
1 1 1 INACTIVE 50
2 1 1 CURRENT 50
3 2 1 INACTIVE 50
4 2 1 CURRENT 50
11 1 1 UNUSED 250
12 1 1 UNUSED 250
13 1 1 UNUSED 250
14 1 1 UNUSED 250
15 2 1 UNUSED 250
9 rows selected.
SQL> select group#,thread#,members,status,bytes/1024/1024 as size_m from v$log;
GROUP# THREAD# MEMBERS STATUS SIZE_M
---------- ---------- ---------- ---------------- ----------
1 1 1 INACTIVE 50
2 1 1 CURRENT 50
3 2 1 INACTIVE 50
4 2 1 CURRENT 50
11 1 1 UNUSED 250
12 1 1 UNUSED 250
13 1 1 UNUSED 250
14 1 1 UNUSED 250
15 2 1 UNUSED 250
16 2 1 UNUSED 250
17 2 1 UNUSED 250
GROUP# THREAD# MEMBERS STATUS SIZE_M
---------- ---------- ---------- ---------------- ----------
18 2 1 UNUSED 250
删除日志组
注：如果要删除的日志组的状态为current或active，让其变为inactive
alter system switch logfile;
alter system checkpoint;
SQL> alter system switch logfile;
System altered.
SQL> select group#,thread#,members,status,bytes/1024/1024 as size_m from v$log;
GROUP# THREAD# MEMBERS STATUS SIZE_M
---------- ---------- ---------- ---------------- ----------
1 1 1 INACTIVE 50
2 1 1 ACTIVE 50
3 2 1 INACTIVE 50
4 2 1 CURRENT 50
11 1 1 CURRENT 250
12 1 1 UNUSED 250
13 1 1 UNUSED 250
14 1 1 UNUSED 250
15 2 1 UNUSED 250
16 2 1 UNUSED 250
17 2 1 UNUSED 250
GROUP# THREAD# MEMBERS STATUS SIZE_M
---------- ---------- ---------- ---------------- ----------
18 2 1 UNUSED 250
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> select group#,thread#,members,status,bytes/1024/1024 as size_m from v$log;
GROUP# THREAD# MEMBERS STATUS SIZE_M
---------- ---------- ---------- ---------------- ----------
1 1 1 CURRENT 50
4 2 1 ACTIVE 50
11 1 1 ACTIVE 250
12 1 1 ACTIVE 250
13 1 1 ACTIVE 250
14 1 1 ACTIVE 250
15 2 1 CURRENT 250
16 2 1 UNUSED 250
17 2 1 UNUSED 250
18 2 1 UNUSED 250
把active变成inactive
SQL> alter system checkpoint;
System altered.
SQL> /
GROUP# THREAD# MEMBERS STATUS SIZE_M
---------- ---------- ---------- ---------------- ----------
4 2 1 INACTIVE 50
11 1 1 INACTIVE 250
12 1 1 CURRENT 250
13 1 1 INACTIVE 250
14 1 1 INACTIVE 250
15 2 1 CURRENT 250
16 2 1 INACTIVE 250
17 2 1 INACTIVE 250
18 2 1 INACTIVE 250
9 rows selected.
SQL> alter database drop logfile group 4;
Database altered.
删除standby log
SQL> alter database drop standby logfile group 5;
Database altered.
SQL> alter database drop standby logfile group 7;
Database altered.
SQL> alter database drop standby logfile group 8;
Database altered.
SQL> alter database drop standby logfile group 9;
Database altered.
SQL> alter database drop standby logfile group 10;
Database altered.
SQL> alter database add standby logfile thread 1 group 1 '+DATA/scprd/onlinelog/standby01.log' size 250M;
Database altered.
SQL> alter database add standby logfile thread 1 group 2 '+DATA/scprd/onlinelog/standby02.log' size 250M;
Database altered.
SQL> alter database add standby logfile thread 1 group 3 '+DATA/scprd/onlinelog/standby03.log' size 250M;
Database altered.
SQL> alter database add standby logfile thread 1 group 4 '+DATA/scprd/onlinelog/standby04.log' size 250M;
Database altered.
SQL> alter database add standby logfile thread 2 group 10 '+DATA/scprd/onlinelog/standby10.log' size 250M ;
alter database add standby logfile thread 2 group 10 '+DATA/scprd/onlinelog/standby10.log' size 250M
*
ERROR at line 1:
ORA-00301: error in adding log file '+DATA/scprd/onlinelog/standby10.log' -
file cannot be created
ORA-17502: ksfdcre:4 Failed to create file +DATA/scprd/onlinelog/standby10.log
ORA-15005: name "scprd/onlinelog/standby10.log" is already used by an existing
alias
SQL> alter database add standby logfile thread 2 group 10 '+DATA/scprd/onlinelog/standby10.log' size 250M reuse;
Database altered.
添加完成日志组。
备库执行：
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Database altered.
开始同步：
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT
Attempt to start background Managed Standby Recovery process (SCPRDDG)
Fri Oct 13 00:10:53 2017
MRP0 started with pid=48, OS id=30390
MRP0: Background Managed Standby Recovery process started (SCPRDDG)
started logmerger process
Fri Oct 13 00:10:58 2017
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 4 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log /oracle/arch/1_45614_930582072.dbf
Media Recovery Log /oracle/arch/2_46396_930582072.dbf
Media Recovery Log /oracle/arch/1_45615_930582072.dbf
Media Recovery Log /oracle/arch/1_45616_930582072.dbf
Media Recovery Log /oracle/arch/1_45617_930582072.dbf
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT
Media Recovery Log /oracle/arch/2_46397_930582072.dbf
Media Recovery Log /oracle/arch/1_45618_930582072.dbf
Media Recovery Log /oracle/arch/1_45619_930582072.dbf
Media Recovery Log /oracle/arch/1_45620_930582072.dbf
Media Recovery Log /oracle/arch/2_46398_930582072.dbf
Media Recovery Log /oracle/arch/1_45621_930582072.dbf
Media Recovery Log /oracle/arch/1_45622_930582072.dbf
Media Recovery Log /oracle/arch/1_45623_930582072.dbf
Media Recovery Log /oracle/arch/2_46399_930582072.dbf
Media Recovery Log /oracle/arch/1_45624_930582072.dbf
SQL> show parameter standby ;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string ?/dbs/arch
standby_file_management string AUTO
SQL> show parameter convert;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string +DATA/scprd/datafile, /oracle/
oradata
log_file_name_convert string +DATA/scprd/onlinelog, /oracle
/oradata
修改DG的同步参数：
SQL> alter system set standby_file_management = manual;
System altered.
SQL> show parameter standby ;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string ?/dbs/arch
standby_file_management string MANUAL
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
手工添加日志组
清理logfile
SQL> alter database clear logfile group 2;
Database altered.
SQL> alter database clear logfile group 3;
Database altered.
SQL> alter database clear logfile group 4;
Database altered.
查看状态：
SQL> select group#,thread#,members,status,bytes/1024/1024 as size_m from v$log;
GROUP# THREAD# MEMBERS STATUS SIZE_M
---------- ---------- ---------- ---------------- ----------
1 1 1 UNUSED 50
2 1 1 UNUSED 50
3 2 1 UNUSED 50
4 2 1 UNUSED 50
添加日志组：
SQL> alter database add logfile thread 1 group 11 '/oracle/oradata/group_11.399.957222953' size 250M;
Database altered.
SQL> alter database add logfile thread 1 group 12 '/oracle/oradata/group_12.400.957223041' size 250M;
Database altered.
SQL> alter database add logfile thread 1 group 13 '/oracle/oradata/group_13.401.957223051' size 250M;
Database altered.
SQL> alter database add logfile thread 1 group 14 '/oracle/oradata/group_14.402.957223059' size 250M;
Database altered.
SQL> alter database add logfile thread 2 group 15 '/oracle/oradata/group_15.403.957223209' size 250M;
Database altered.
SQL> alter database add logfile thread 2 group 16 '/oracle/oradata/group_16.404.957223315' size 250M;
Database altered.
SQL> alter database add logfile thread 2 group 17 '/oracle/oradata/group_17.405.957223323' size 250M;
Database altered.
SQL> alter database add logfile thread 2 group 18 '/oracle/oradata/group_18.406.957223331' size 250M;
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> alter database drop standby logfile group 8;
Database altered.
SQL> select group#,thread#,members,status,bytes/1024/1024 as size_m from v$log;
GROUP# THREAD# MEMBERS STATUS SIZE_M
---------- ---------- ---------- ---------------- ----------
1 1 1 UNUSED 50
2 1 1 UNUSED 50
12 1 1 CURRENT 250
14 1 1 UNUSED 250
13 1 1 UNUSED 250
11 1 1 UNUSED 250
16 2 1 UNUSED 250
17 2 1 UNUSED 250
18 2 1 UNUSED 250
15 2 1 CURRENT 250
4 2 1 UNUSED 50
GROUP# THREAD# MEMBERS STATUS SIZE_M
---------- ---------- ---------- ---------------- ----------
3 2 1 UNUSED 50
12 rows selected.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database drop logfile group 4;
Database altered.
SQL> select group#,thread#,members,status,bytes/1024/1024 as size_m from v$log;
GROUP# THREAD# MEMBERS STATUS SIZE_M
---------- ---------- ---------- ---------------- ----------
11 1 1 UNUSED 250
12 1 1 CURRENT 250
13 1 1 UNUSED 250
14 1 1 UNUSED 250
15 2 1 CURRENT 250
16 2 1 UNUSED 250
17 2 1 UNUSED 250
18 2 1 UNUSED 250
8 rows selected.
SQL> alter database drop standby logfile group 9;
Database altered.
SQL> alter database drop standby logfile group 10;
Database altered.
添加standby log组：
SQL> alter database add standby logfile thread 1 group 1 '/oracle/oradata/standby01.log' size 250M;
Database altered.
SQL> alter database add standby logfile thread 1 group 2 '/oracle/oradata/standby02.log' size 250M;
Database altered.
SQL> alter database add standby logfile thread 1 group 3 '/oracle/oradata/standby03.log' size 250M;
Database altered.
SQL> alter database add standby logfile thread 1 group 4 '/oracle/oradata/standby04.log' size 250M;
Database altered.
SQL> alter database add standby logfile thread 1 group 5 '/oracle/oradata/standby05.log' size 250M;
alter database add standby logfile thread 1 group 5 '/oracle/oradata/standby05.log' size 250M
*
ERROR at line 1:
ORA-00301: error in adding log file '/oracle/oradata/standby05.log' - file
cannot be created
ORA-27038: created file already exists
Additional information: 1
SQL> alter database add standby logfile thread 1 group 5 '/oracle/oradata/standby05.log' size 250M reuse;
Database altered.
SQL> alter database add standby logfile thread 2 group 6 '/oracle/oradata/standby06.log' size 250M;
alter database add standby logfile thread 2 group 6 '/oracle/oradata/standby06.log' size 250M
*
ERROR at line 1:
ORA-00301: error in adding log file '/oracle/oradata/standby06.log' - file
cannot be created
ORA-27038: created file already exists
Additional information: 1
SQL> alter database add standby logfile thread 2 group 6 '/oracle/oradata/standby06.log' size 250M reuse;
Database altered.
SQL> alter database add standby logfile thread 2 group 7 '/oracle/oradata/standby07.log' size 250M;
alter database add standby logfile thread 2 group 7 '/oracle/oradata/standby07.log' size 250M
*
ERROR at line 1:
ORA-00301: error in adding log file '/oracle/oradata/standby07.log' - file
cannot be created
ORA-27038: created file already exists
Additional information: 1
SQL> alter database add standby logfile thread 2 group 7 '/oracle/oradata/standby07.log' size 250M reuse;
Database altered.
SQL> alter database add standby logfile thread 2 group 8 '/oracle/oradata/standby08.log' size 250M reuse;
Database altered.
SQL> alter database add standby logfile thread 2 group 9 '/oracle/oradata/standby09.log' size 250M reuse;
Database altered.
SQL> alter database add standby logfile thread 2 group 10 '/oracle/oradata/standby10.log' size 250M reuse;
Database altered.
备库开启同步：
SQL> alter system set standby_file_management = AUTO;
System altered.
SQL> show parameter standby;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string ?/dbs/arch
standby_file_management string AUTO
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Database altered.
用切日志看主备是不是同步正常。
SQL> alter system switch logfile;
System altered.