DBA Hub

📋Steps in this guide1/4

How to drop the standby redo logs

How to drop the standby redo logs 0. Enviroment 1. SET STANDBY_FILE_MANAGEMENT=’MANUAL’ 2. Drop SRL On Standby 3. SET STANDBY_FILE_MANAGEMENT=’AUTO’ 4. Enable MRP 0. Enviroment Source: Platform: Linuxx86_64 Server Name: RAC1.RAJASEKHAR.COM, IP: 192.168.2.101 DB Version: Oracle 11.2.0.3, File system: ASM DB Name: APAC, DB_UNIQUE_NAME: APAC Flashback: Disabled Oracle Home Path: /u01/app/oracle/produ

oracle clusteringintermediate
by OracleDba
15 views
1

Overview

0. Enviroment 1. SET STANDBY_FILE_MANAGEMENT=’MANUAL’ On Standby

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Source:

		Platform: Linuxx86_64
		Server Name: RAC1.RAJASEKHAR.COM, IP: 192.168.2.101
		DB Version: Oracle 11.2.0.3, File system: ASM
		DB Name: APAC, DB_UNIQUE_NAME: APAC
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1
		
Target:
		
		Platform: Linuxx86_64
		Server Name: RAC2.RAJASEKHAR.COM, IP: 192.168.2.102
		DB Version: Oracle 11.2.0.3, , File system: ASM
		DB Name: APAC, DB_UNIQUE_NAME: EMEA
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1
2

Section 2

2. Drop SRL On Standby Note: If the status of standby redolog show the "clearing_current" then we cannot drop "clearing_current" status logs,and for that we have to sync with Primary and clear the log first before dropping as below 3. SET STANDBY_FILE_MANAGEMENT=’AUTO’

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
SQL> select name, open_mode, database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
APAC      READ ONLY WITH APPLY PHYSICAL STANDBY

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                                       IS_
---------- ------- ------- ------------------------------------------------------------ ---
         3         ONLINE  /u01/app/oracle/oradata/emea/redo03.log                      NO
         2         ONLINE  /u01/app/oracle/oradata/emea/redo02.log                      NO
         1         ONLINE  /u01/app/oracle/oradata/emea/redo01.log                      NO
         4         STANDBY /u01/app/oracle/oradata/emea/redo04.log                      NO
         5         STANDBY /u01/app/oracle/oradata/emea/redo05.log                      NO
         6         STANDBY /u01/app/oracle/oradata/emea/redo06.log                      NO
         7         STANDBY /u01/app/oracle/oradata/emea/redo07.log                      NO

7 rows selected.

SQL>
SQL> alter database drop standby logfile group 4;
alter database drop standby logfile group 4
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files
SQL>
alter database recover managed standby database cancel;
Database altered.

SQL> select name, open_mode, database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
APAC      READ ONLY            PHYSICAL STANDBY

SQL> 

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL';

System altered.

SQL>

SQL> alter database drop standby logfile group 4;
alter database drop standby logfile group 4
*
ERROR at line 1:
ORA-00261: log 4 of thread 1 is being archived or modified
ORA-00312: online log 4 thread 1: '/u01/app/oracle/oradata/emea/redo04.log'
SQL>
Note: If the status of standby redolog show the "clearing_current" then we cannot drop "clearing_current" status logs,and for that we have to sync with Primary and clear the log first before dropping as below
SQL>
alter database clear logfile group 4;
Database altered.

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> select * from v$logfile;

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

SQL> select * from v$standby_log;

no rows selected

SQL>
3

Section 3

3. SET STANDBY_FILE_MANAGEMENT=’AUTO’ 4. Enable MRP Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO' SCOPE=BOTH;

System altered.

SQL>

SQL>
alter database recover managed standby database disconnect from session;
Database altered.

SQL> select name, open_mode, database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
APAC      READ ONLY WITH APPLY PHYSICAL STANDBY

SQL>
4

Section 4

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Comments (0)

Please to add comments

No comments yet. Be the first to comment!