Oracle DataGuard – How to resize Redo Log and Standby Redo Log



Oracle DataGuard – How to resize Redo Log and Standby Redo Log

set linesize 200
col dest_name for a30
select DEST_NAME,STATUS,PROTECTION_MODE,ARCHIVED_SEQ#,APPLIED_SEQ# from v$archive_dest_status;

On Standby server - make file management parameter to manual if not:
show parameter standby;
alter system set standby_file_management=manual;
==================
+     On Primary             +
==================
 SQL> select group#,thread#,status from v$log;

col member for a60
set linesize 200
select member,group#,status from v$logfile;
SQL> select group#,thread#,status from v$log;

    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 ACTIVE
         2          1 INACTIVE
         3          1 CURRENT

Online Redo Log groups 2 is INACTIVE. Hence we can drop that and re-create with new size and file path.

SQL> alter database drop logfile group  2;

Database altered.

SQL> alter database add logfile thread 1 group 2 size 100M;

Database altered.
alter system switch logfile;
alter system checkpoint;

-----------------------------------------------------------
DO SAME FOR OTHERS LOG GROUPS.
-----------------------------------------------------------
SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$log group by group#;

    GROUP# size in MB
---------- ----------
         1        100
         2        100
         3        100

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

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 add standby logfile thread 1 group 4 size 100M;
Database altered.

SQL> alter database add standby logfile thread 1 group 5 size 100M;
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 size 100M;
Database altered.

SQL> alter database add standby logfile thread 1 group 7 size 100M;
Database altered.

==================
+     STANDBY SIDE   +
===================

SQL>  select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;

    GROUP# size in MB
---------- ----------
         4        100
         5        100
         6        100
         7        100

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

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         3 CLEARING
         2 CLEARING

SQL>  alter database recover managed standby database cancel;

Database altered.

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

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         3 CLEARING
         2 CLEARING
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> /

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         3 CLEARING
         2 CLEARING

So Ican recreate for Group 2 and 3 [as Clearing State]

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 size 100M;
Database altered.

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

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         3 CLEARING
         2 UNUSED

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 size 100M;
Database altered.

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

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         3 UNUSED
         2 UNUSED

NOTE: 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;

On standby server:
------------------------------

SQL> /

    GROUP# STATUS
---------- ----------------
         1 CLEARING
         3 CURRENT
         2 UNUSED

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 size 100M;
Database altered.

SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$log group by group#;

    GROUP# size in MB
---------- ----------
         1        100
         2        100
         3        100

SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;

    GROUP# size in MB
---------- ----------
         4        100
         5        100
         6        100
         7        100


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 size 100M;
 alter database add standby logfile thread 1 group 5 size 100M;
 alter database add standby logfile thread 1 group 6 size 100M;
 alter database add standby logfile thread 1 group 7 size 100M;
 Database altered.


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 scope=both;
System altered.

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

SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
         14173

Check the the standby logs are being used by running following query :


set lines 155 pages 9999
col thread# for 9999990
col sequence# for 999999990
col grp for 990
col fnm for a50 head "File Name"
col "Fisrt SCN Number" for 999999999999990
break on thread
# skip 1
select a.thread#
,a.sequence#
,a.group# grp    
, a.bytes/1024/1024 Size_MB    
,a.status    
,a.archived    
,a.first_change# "First SCN Number"    
,to_char(FIRST_TIME,'DD-Mon-RR HH24:MI:SS') "First SCN Time"  
,to_char(LAST_TIME,'DD-Mon-RR HH24:MI:SS') "Last SCN Time"  from
 v$standby_log a  order by 1,2,3,4
 /

Sample Output:

 THREAD#  SEQUENCE#  GRP    SIZE_MB STATUS     ARC First SCN Number First SCN Time              Last SCN Time
-------- ---------- ---- ---------- ---------- --- ---------------- --------------------------- ---------------------------
       0          0    5        100 UNASSIGNED YES
       0          0    6        100 UNASSIGNED YES
       0          0    7        100 UNASSIGNED YES
       1      14176    4        100 ACTIVE     YES         62194009 28-Oct-15 14:50:45          28-Oct-15 14:54:12

Comments

Post a Comment

Popular posts from this blog

Exadata - Smart Scan Testing

Image
Exadata - SMART SCAN testing -------------------------------------------- Samrt scan works with cell offloading which relate to offloaded the sql processing from the database layer to the cell (storage) layer. Smart scan is the access mechanism to offload data from storage layer. For example passing only required data blocks after applying predicate filters at the cell layer instead of sending every possible blocks from cell server to database server.                           Figure 1: Traditional Database Storage I/O and SQL Processing Model   Process Steps: ------------------- 1.    The client issues a SELECT statement . 2.    The database kernel maps this request to the database files and extents which contain the table data. 3.    The database kernel issues the I/Os to read all the table bloc...
Read more

Exadata Flash Cache - Management

Total size of Flash CellCLI>  list flashcache attributes effectiveCacheSize          5.82122802734375T Flashcache Mode:  [root@Cellc01-adm ~]# su - celladmin cellcli CellCLI>  list cell attributes flashCacheMode,status          WriteThrough    online > list celldisk where disktype=flashdisk > list physicaldisk FLASH_1_1 detail List PhysicalDisk for FlashCache: CellCLI> list physicaldisk attributes name, disktype, makemodel, physicalrpm, physicalport, status      8:0           HardDisk      "HGST    H7240AS60SUN4.0T"                              normal      8:1  ...
Read more