====== RESIZE REDO LOGS ======
* size Online REDOLOG
set lines 400
col MEMBER format A60
SELECT a.group#,b.THREAD#, b.STATUS,a.type,
b.BYTES/1024/1024 "Size (Mb)"
FROM v$logfile a,
v$log b
WHERE a.group# = b.group#;
* size StandBy REDOLOG
set lines 300
select group#, thread#, sequence#, BYTES/1024/1024 "Size (Mb)", status from v$standby_log;
* Localisation
set lines 300
select * from v$logfile
order by GROUP#, TYPE;
\\
== PRIMAIRE ==
* active Redo Group
SELECT * FROM V$LOG;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
1 1 4272 4294967296 512 1 YES INACTIVE ########## 26-MAR-24 5.1611E+12 26-MAR-24 0
2 1 4273 4294967296 512 1 NO CURRENT ########## 26-MAR-24 9.2954E+18 0
3 2 4295 4294967296 512 1 YES INACTIVE ########## 26-MAR-24 5.1611E+12 26-MAR-24 0
4 2 4296 4294967296 512 1 NO CURRENT ########## 26-MAR-24 9.2954E+18 0
* on ajoute les nouveaux Redo
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 SIZE 2G;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 6 SIZE 2G;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 7 SIZE 2G;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 8 SIZE 2G;
* sur la primaire, drop des standby redo et recreation
alter database drop logfile group 5 ;
alter database drop logfile group 6 ;
alter database drop logfile group 7 ;
alter database drop logfile group 8 ;
alter database drop logfile group 101 ;
alter database drop logfile group 102 ;
alter database drop logfile group 103 ;
alter database drop logfile group 201 ;
alter database drop logfile group 202 ;
alter database drop logfile group 203 ;
alter database add standby logfile thread 1 group 101 size 2048M;
alter database add standby logfile thread 1 group 102 size 2048M;
alter database add standby logfile thread 2 group 103 size 2048M;
alter database add standby logfile thread 2 group 104 size 2048M;
* drop unused ONLINE REDO
alter database drop standby logfile group 1;
alter database drop standby logfile group 3;
* rotation + checkpoint si current
alter system switch logfile;
ALTER SYSTEM CHECKPOINT GLOBAL;
select * from v$log;
* drop des autres redo apres rotation
alter database drop logfile group 2;
alter database drop logfile group 4;
* on active standby_file_management
alter system set standby_file_management=AUTO scope=both sid='*';
* on check eventuellemt des reste de REDO d'install précédentes
asmcmd ls -l */C1EDPDEVDR/ONLINELOG/
\\
== STDY ==
* On stoppe le TRANSPORT et APPLY dans le broker
dgmgrl /
edit database c1edpdev set state='TRANSPORT-OFF';
edit database c1edpdevdr set state='APPLY-OFF';
* on désactive standby_file_management
sqlpus / as sysdba
alter system set standby_file_management=MANUAL scope=both sid='*';
select process, status, thread#, sequence#, block#, blocks from v$managed_standby where process like '%MRP%';
no rows selected
* Size et statut
set lines 400
col MEMBER format A60
SELECT a.group#, b.STATUS,a.type,
a.MEMBER,
b.BYTES/1024/1024 "Size (Mb)"
FROM v$logfile a,
v$log b
WHERE a.group# = b.group#;
* Redo Group
set lines 400
SELECT * FROM V$LOG;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
1 1 0 4294967296 512 1 YES UNUSED 5.1610E+12 13-MAR-24 9.2954E+18 0
2 1 0 4294967296 512 1 YES UNUSED 5.1610E+12 13-MAR-24 5.1610E+12 13-MAR-24 0
3 2 0 4294967296 512 1 YES UNUSED 5.1610E+12 13-MAR-24 5.1610E+12 13-MAR-24 0
4 2 0 4294967296 512 1 YES UNUSED 5.1610E+12 13-MAR-24 9.2954E+18 0
* REDO FILES
set lines 400
col MEMBER format A60
SELECT * FROM V$LOGFILE;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- ------------------------------------------------------------ --- ----------
2 ONLINE +REDO/C1EDPDEVDR/ONLINELOG/group_2.299.1163523547 NO 0
1 ONLINE +REDO/C1EDPDEVDR/ONLINELOG/group_1.300.1163523545 NO 0
3 ONLINE +REDO/C1EDPDEVDR/ONLINELOG/group_3.298.1163523551 NO 0
4 ONLINE +REDO/C1EDPDEVDR/ONLINELOG/group_4.297.1163523553 NO 0
101 STANDBY +REDO/C1EDPDEVDR/ONLINELOG/group_101.306.1163523567 NO 0
102 STANDBY +REDO/C1EDPDEVDR/ONLINELOG/group_102.307.1163523571 NO 0
103 STANDBY +REDO/C1EDPDEVDR/ONLINELOG/group_103.308.1163523573 NO 0
201 STANDBY +REDO/C1EDPDEVDR/ONLINELOG/group_201.309.1163523575 NO 0
202 STANDBY +REDO/C1EDPDEVDR/ONLINELOG/group_202.310.1163523579 NO 0
203 STANDBY +REDO/C1EDPDEVDR/ONLINELOG/group_203.311.1163523581 NO 0
5 STANDBY +REDO/C1EDPDEVDR/ONLINELOG/group_5.296.1163523557 NO 0
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- ------------------------------------------------------------ --- ----------
6 STANDBY +REDO/C1EDPDEVDR/ONLINELOG/group_6.295.1163523559 NO 0
7 STANDBY +REDO/C1EDPDEVDR/ONLINELOG/group_7.304.1163523563 NO 0
8 STANDBY +REDO/C1EDPDEVDR/ONLINELOG/group_8.305.1163523565 NO 0
* drop des standby/online redo et recreation
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
alter database drop logfile group 4;
alter database drop logfile group 5;
alter database drop logfile group 101 ;
alter database drop logfile group 102 ;
alter database drop logfile group 103 ;
alter database drop logfile group 201 ;
alter database drop logfile group 202 ;
alter database drop logfile group 203 ;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 1 SIZE 2G;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 2 SIZE 2G;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 3 SIZE 2G;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 4 SIZE 2G;
alter database add standby logfile thread 1 group 101 size 2048M;
alter database add standby logfile thread 1 group 102 size 2048M;
alter database add standby logfile thread 2 group 103 size 2048M;
alter database add standby logfile thread 2 group 104 size 2048M;
* si ORA-00261: log 3 of thread 2 is being archived or modified
alter database clear logfile group x;
alter database drop logfile group x;
* on relance le TRANSPORT et APPLY
alter system set dg_broker_start=true SCOPE=BOTH sid='*';
alter system set standby_file_management=AUTO scope=both sid='*';
edit database c1edpdev set state='TRANSPORT-ON';
edit database c1edpdevdr set state='APPLY-ON';