Refer to Doc in Metalink and test in on Prod.
I have one physical standby database in sync with the primary database with the logs being applied in the form of real time apply. As you might be aware, for the “Real Time Apply” implementation, we need to make sure that we have created the Standby Redo Logs on the standby database with the size same as that of the Online Redo logs and have one more standby redo log group than redo group for better performance. The Standby Redo Logs also needs to be created on the primary database, but this is not mandatory. The Standby Redo Logs would not be used on the Primary database until there is a switchover operation performed and the primary database starts behaving as a standby database. Here is a complete steps snippet of the resize of the redo logs (Online and Standby Redo logs) on the primary and standby database.
On Primary:
/oradata/home/oracle >$sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 20 02:30:30 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
--Check if primary and standby are in sync or out of sync
SQL> set linesize 200
SQL> col dest_name for a30
SQL> select DEST_NAME,STATUS,PROTECTION_MODE,ARCHIVED_SEQ#,APPLIED_SEQ# from v$archive_dest_status;
DEST_NAME STATUS PROTECTION_MODE ARCHIVED_SEQ# APPLIED_SEQ#
------------------------------ --------- -------------------- ------------- ------------
LOG_ARCHIVE_DEST_1 INACTIVE MAXIMUM PERFORMANCE 3474 0
LOG_ARCHIVE_DEST_2 VALID MAXIMUM PERFORMANCE 3474 0
LOG_ARCHIVE_DEST_3 VALID MAXIMUM PERFORMANCE 3474 0
LOG_ARCHIVE_DEST_4 VALID MAXIMUM AVAILABILITY 3474 3473
LOG_ARCHIVE_DEST_5 INACTIVE MAXIMUM PERFORMANCE 3474 0
LOG_ARCHIVE_DEST_6 INACTIVE MAXIMUM PERFORMANCE 3474 0
LOG_ARCHIVE_DEST_7 INACTIVE MAXIMUM PERFORMANCE 3474 0
LOG_ARCHIVE_DEST_8 INACTIVE MAXIMUM PERFORMANCE 3474 0
LOG_ARCHIVE_DEST_9 INACTIVE MAXIMUM PERFORMANCE 3474 0
LOG_ARCHIVE_DEST_10 INACTIVE MAXIMUM PERFORMANCE 3474 0
LOG_ARCHIVE_DEST_11 INACTIVE MAXIMUM PERFORMANCE 3474 0
LOG_ARCHIVE_DEST_12 INACTIVE MAXIMUM PERFORMANCE 3474 0
LOG_ARCHIVE_DEST_13 INACTIVE MAXIMUM PERFORMANCE 3474 0
LOG_ARCHIVE_DEST_14 INACTIVE MAXIMUM PERFORMANCE 3474 0
LOG_ARCHIVE_DEST_15 INACTIVE MAXIMUM PERFORMANCE 3474 0
LOG_ARCHIVE_DEST_16 INACTIVE MAXIMUM PERFORMANCE 3474 0
LOG_ARCHIVE_DEST_17 INACTIVE MAXIMUM PERFORMANCE 3474 0
LOG_ARCHIVE_DEST_18 INACTIVE MAXIMUM PERFORMANCE 3474 0
LOG_ARCHIVE_DEST_19 INACTIVE MAXIMUM PERFORMANCE 3474 0
LOG_ARCHIVE_DEST_20 INACTIVE MAXIMUM PERFORMANCE 3474 0
LOG_ARCHIVE_DEST_21 INACTIVE MAXIMUM PERFORMANCE 3474 0
LOG_ARCHIVE_DEST_22 INACTIVE MAXIMUM PERFORMANCE 3474 0
LOG_ARCHIVE_DEST_23 INACTIVE MAXIMUM PERFORMANCE 3474 0
LOG_ARCHIVE_DEST_24 INACTIVE MAXIMUM PERFORMANCE 3474 0
LOG_ARCHIVE_DEST_25 INACTIVE MAXIMUM PERFORMANCE 3474 0
LOG_ARCHIVE_DEST_26 INACTIVE MAXIMUM PERFORMANCE 3474 0
LOG_ARCHIVE_DEST_27 INACTIVE MAXIMUM PERFORMANCE 3474 0
LOG_ARCHIVE_DEST_28 INACTIVE MAXIMUM PERFORMANCE 3474 0
LOG_ARCHIVE_DEST_29 INACTIVE MAXIMUM PERFORMANCE 3474 0
LOG_ARCHIVE_DEST_30 INACTIVE MAXIMUM PERFORMANCE 3474 0
LOG_ARCHIVE_DEST_31 INACTIVE MAXIMUM PERFORMANCE 3474 0
31 rows selected.
--Check if the parameter standby_file_management is set to AUTO or MANUAL on standby database. If it is not set to MANUAL, then set it.
On Standby server:
/oradata/home/oracle >$sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 20 02:29:24 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter standby;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string ?/dbs/arch
standby_file_management string AUTO
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
On Primary :
--Below you can see that Online Redo Log groups 1 and 2 are INACTIVE. Hence we can drop them and re-create with the new size, and check the log file path as well.
SQL> select group#,thread#,status from v$log;
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 INACTIVE
2 1 INACTIVE
3 1 CURRENT
SQL> col member for a60
SQL> set linesize 200
SQL> select member,group#,status from v$logfile;
MEMBER GROUP# STATUS
------------------------------------------------------------ ---------- -------
/oradata/oraredo/isuite/dsk01/redo1_01_01.rdo 1
/oradata/oraredo/isuite/dsk02/redo1_01_02.rdo 1
/oradata/oraredo/isuite/dsk01/redo1_02_01.rdo 2
/oradata/oraredo/isuite/dsk02/redo1_02_02.rdo 2
/oradata/oraredo/isuite/dsk01/redo1_03_01.rdo 3
/oradata/oraredo/isuite/dsk02/redo1_03_02.rdo 3
/oradata/oraredo/isuite/dsk01/stdbylog01_04_01.rdo 4
/oradata/oraredo/isuite/dsk02/stdbylog01_04_02.rdo 4
/oradata/oraredo/isuite/dsk01/stdbylog01_05_01.rdo 5
/oradata/oraredo/isuite/dsk02/stdbylog01_05_02.rdo 5
/oradata/oraredo/isuite/dsk01/stdbylog01_06_01.rdo 6
/oradata/oraredo/isuite/dsk02/stdbylog01_06_02.rdo 6
/oradata/oraredo/isuite/dsk01/stdbylog01_07_01.rdo 7
/oradata/oraredo/isuite/dsk02/stdbylog01_07_02.rdo 7
14 rows selected.
SQL> select group#,thread#,status from v$log;
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 INACTIVE
2 1 INACTIVE
3 1 CURRENT
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile thread 1 group 1 ('/oradata/oraredo/isuite/dsk01/redo1_1.log','/oradata/oraredo/isuite/dsk02/redo1_2.log') size 200M;
Database altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 UNUSED
2 INACTIVE
3 CURRENT
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database add logfile thread 1 group 2 ('/oradata/oraredo/isuite/dsk01/redo2_1.log','/oradata/oraredo/isuite/dsk02/redo2_2.log') size 200M;
Database altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 UNUSED
2 UNUSED
3 CURRENT
--Now that Online Redo Log Groups 1 and 2 are resized and the status of Group 3 is CURRENT, switch logfiles manually until Group 3 becomes INACTIVE. After a couple of log switches, we can check the Status of Group 3
SQL> alter system switch logfile;
System altered.
SQL> select group#,thread#,status from v$log;
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 CURRENT
2 1 UNUSED
3 1 ACTIVE
SQL> select group#,thread#,status from v$log;
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 CURRENT
2 1 UNUSED
3 1 ACTIVE
SQL> alter system switch logfile;
System altered.
SQL> select group#,thread#,status from v$log;
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 ACTIVE
2 1 CURRENT
3 1 ACTIVE
SQL> alter system switch logfile;
System altered.
SQL> select group#,thread#,status from v$log;
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 ACTIVE
2 1 ACTIVE
3 1 CURRENT
SQL> select group#,thread#,status from v$log;
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 ACTIVE
2 1 ACTIVE
3 1 CURRENT
SQL> select group#,thread#,status from v$log;
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 ACTIVE
2 1 ACTIVE
3 1 CURRENT
SQL> /
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 ACTIVE
2 1 ACTIVE
3 1 CURRENT
SQL> /
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 ACTIVE
2 1 ACTIVE
3 1 CURRENT
SQL> /
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 ACTIVE
2 1 ACTIVE
3 1 CURRENT
SQL> /
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 ACTIVE
2 1 ACTIVE
3 1 CURRENT
SQL> /
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 ACTIVE
2 1 ACTIVE
3 1 CURRENT
SQL> /
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 ACTIVE
2 1 ACTIVE
3 1 CURRENT
SQL> /
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 ACTIVE
2 1 ACTIVE
3 1 CURRENT
SQL> /
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 ACTIVE
2 1 ACTIVE
3 1 CURRENT
SQL> /
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 ACTIVE
2 1 ACTIVE
3 1 CURRENT
SQL> /
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 CURRENT
2 1 INACTIVE
3 1 INACTIVE
--Now that Group 3 is INACTIVE, we can drop it and re-create it with the new size.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile thread 1 group 3 ('/oradata/oraredo/isuite/dsk01/redo3_1.log','/oradata/oraredo/isuite/dsk02/redo3_2.log') size 200M;
Database altered.
SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$log group by group#;
GROUP# size in MB
---------- ----------
1 200
2 200
3 200
--Now, we have resized all the Online Redo Logs on the Primary Database from 50M to 200M.
--Moving on to the Standby Redo Logs on the Primary Database:
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
--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 drop standby logfile group 5;
Database altered.
SQL> alter database add standby logfile thread 1 group 4('/oradata/oraredo/isuite/dsk01/standby4_1.log','/oradata/oraredo/isuite/dsk02/standby4_2.log') size 200M;
Database altered.
SQL> alter database add standby logfile thread 1 group 5('/oradata/oraredo/isuite/dsk01/standby5_1.log','/oradata/oraredo/isuite/dsk02/standby5_2.log') size 200M;
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 add standby logfile thread 1 group 6('/oradata/oraredo/isuite/dsk01/standby6_1.log','/oradata/oraredo/isuite/dsk02/standby6_2.log') size 200M;
alter database add standby logfile thread 1 group 7('/oradata/oraredo/isuite/dsk01/standby7_1.log','/oradata/oraredo/isuite/dsk02/standby7_2.log') size 200M;
Database altered.
SQL>
Database altered.
SQL>
SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;
GROUP# size in MB
---------- ----------
6 200
4 200
5 200
7 200
--so far all standby log were resized.
Move on physical standby server:
Last login: Wed Mar 19 03:02:43 2014 from 10.35.24.211
#########################################################################
# Please check that the time the system shows you last #
# logged in is correct. #
# #
# If it is not please change your password and contact the #
# system administrator immediately #
#########################################################################
root@nycs-oradb01 # su - oracle
#########################################################################
# Please check that the time the system shows you last #
# logged in is correct. #
# #
# If it is not please change your password and contact the #
# system administrator immediately #
#########################################################################
SQL> recover managed standby database disconnect from session;
ORA-01153: an incompatible media recovery is active
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$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 CLEARING
3 CLEARING
--Lets try to drop Online Redo Log Group 2 as Group 1 is CURRENT status and it cannot be dropped.
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
--Here above, we faced ORA-01156 error, which is self-explanatory. The recovery (MRP process) on the standby database is active and it needs to be cancelled before we drop any Redo log groups.
SQL> alter database recover managed standby database cancel;
Database altered.
--Since the status of Group 2 is still clearing, lets clear it manually before dropping the group.
SQL> alter database clear logfile group 2;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database add logfile thread 1 group 2 ('/oradata/oraredo/isuite/dsk01/redo2_1.log','/oradata/oraredo/isuite/dsk02/redo2_2.log') size 200M;
Database altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 UNUSED
3 CLEARING
--The same is the case with Group 3 whose status is CLEARING. We’ll clear it manually, drop it and create with the new size.
SQL> alter database clear logfile group 3;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile thread 1 group 3 ('/oradata/oraredo/isuite/dsk01/redo3_1.log','/oradata/oraredo/isuite/dsk02/redo3_2.log') size 200M;
Database altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 UNUSED
3 UNUSED
-- To drop online redo log group 1 that is in status CURRENT, generate an archive on the primary database by switching logfile, and then clear the CURRENT online redo log group on the standby database, drop it and re-create it with the required size as done for groups 2 and 3 earlier.
Go to primary database:
SQL> alter system switch logfile;
System altered.
On standby server:
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CLEARING
2 CURRENT
3 CLEARING
SQL> alter database clear logfile group 1;
Database altered.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile thread 1 group 1 ('/oradata/oraredo/isuite/dsk01/redo1_1.log','/oradata/oraredo/isuite/dsk02/redo1_2.log') size 200M;
Database altered.
SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$log group by group#;
GROUP# size in MB
---------- ----------
1 200
2 200
3 200
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 UNUSED
2 CURRENT
3 CLEARING
--Resizing Standby Redo Logs on standby database:
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
-- Clear the SRL group If 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. In our case none of standby log ‘s status is ACTIVE
--For the SRL groups whose status is UNASSIGNED, just drop the group and recreate 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 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 add standby logfile thread 1 group 4('/oradata/oraredo/isuite/dsk01/standby4_1.log','/oradata/oraredo/isuite/dsk02/standby4_2.log') size 200M;
alter database add standby logfile thread 1 group 5('/oradata/oraredo/isuite/dsk01/standby5_1.log','/oradata/oraredo/isuite/dsk02/standby5_2.log') size 200M;
alter database add standby logfile thread 1 group 6('/oradata/oraredo/isuite/dsk01/standby6_1.log','/oradata/oraredo/isuite/dsk02/standby6_2.log') size 200M;
alter database add standby logfile thread 1 group 7('/oradata/oraredo/isuite/dsk01/standby7_1.log','/oradata/oraredo/isuite/dsk02/standby7_2.log') size 200M;
Database altered.
SQL>
Database altered.
SQL>
Database altered.
SQL>
Database altered.
SQL>
SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;
GROUP# size in MB
---------- ----------
6 200
4 200
5 200
7 200
-- Once all the Standby Redo Logs and Online Redo Logs have been resize on both Primary and standby database, set the STANDBY_FILE_MANAGEMENT to AUTO on the standby database and start the recovery (MRP) on standby database.
SQL> alter system set standby_file_management=auto;
System altered.
SQL> alter system set standby_file_management=auto scope=both;
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 3475
ARCH CLOSING 3473
ARCH CONNECTED 0
ARCH CLOSING 3472
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
MRP0 WAIT_FOR_LOG 3480
8 rows selected.
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CLOSING 3475
ARCH CLOSING 3483
ARCH CONNECTED 0
ARCH CLOSING 3482
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
MRP0 APPLYING_LOG 3484
RFS IDLE 3484
9 rows selected.
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
3482
On primary:
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
3483
On standby:
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
3482
Hereto all steps has been completed
Note:
To clear the ACTIVE SRL, you can either clear it on the standby database by using:
SQL>alter database clear logfile group ;
drop it and recreate with a new size or you can do log switches on the primary database and check if the ACTIVE standby redo log group has turned out to be UNASSIGNED, later drop it and recreate with the new size. I prefer the second way (log switches on primary, dropping UNASSIGNED SRL on standby, recreating it with new size) of going for the ACTIVE SRL rather than clearing it, dropping and recreating.
You can refer to log clear for detailed explanation.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/36779/viewspace-1126732/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/36779/viewspace-1126732/
932
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
