Oracle Database Commands and Practical Examples: Online Redo log file drop and recreate

SQL> select GROUP#,THREAD#,STATUS from v$log;

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

SQL> @d

NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN
--------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ----------------------------------------
SMRPATCH SMRPATCH7 SMRPATCH7 READ WRITE PRIMARY NO 0

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/SMRPATCH7/redo03.log
/u01/app/oracle/oradata/SMRPATCH7/redo02.log
/u01/app/oracle/oradata/SMRPATCH7/redo01.log

SQL> @d

NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN
--------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ----------------------------------------
SMRPATCH SMRPATCH7 SMRPATCH7 READ WRITE PRIMARY NO 0

SQL> alter database drop logfile group 1;

Database altered.

SQL> select GROUP#,THREAD#,STATUS from v$log;

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

SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance SMRPATCH7 (thread 1) - cannot drop
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/SMRPATCH7/redo03.log'

SQL> alter database add logfile group 1('/u04/MasterDB/oradata/SMRPATCH7/redo01.log')size 50m;

Database altered.

SQL> select GROUP#,THREAD#,STATUS from v$log;

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

SQL> alter database drop logfile group 2;

Database altered.

SQL> select GROUP#,THREAD#,STATUS from v$log;

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

SQL> alter database add logfile group 2('/u04/MasterDB/oradata/SMRPATCH7/redo02.log')size 50m;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> select GROUP#,THREAD#,STATUS from v$log;

GROUP# THREAD# STATUS

---------- ---------- ----------------

1 1 CURRENT

2 1 INACTIVE

3 1 INACTIVE

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database add logfile group 1('/u04/MasterDB/oradata/SMRPATCH7/redo02.log')size 50m;

alter database add logfile group 1('/u04/MasterDB/oradata/SMRPATCH7/redo02.log')size 50m

ERROR at line 1:

ORA-01184: logfile group 1 already exists

SQL> alter database add logfile group 1('/u04/MasterDB/oradata/SMRPATCH7/redo03.log')size 50m;

alter database add logfile group 1('/u04/MasterDB/oradata/SMRPATCH7/redo03.log')size 50m

ERROR at line 1:

ORA-01184: logfile group 1 already exists

SQL> alter database add logfile group 3 ('/u04/MasterDB/oradata/SMRPATCH7/redo03.log')size 50m;

Database altered.

SQL> select member from v$logfile;

MEMBER

/u04/MasterDB/oradata/SMRPATCH7/redo03.log

/u04/MasterDB/oradata/SMRPATCH7/redo02.log

/u04/MasterDB/oradata/SMRPATCH7/redo01.log

SQL> exit

#################### 2 node RAC Example ##############################

SQL>

NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN

--------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ----------------------------------------

SMRPROD SMRPROD1 SMRPROD READ WRITE PRIMARY NO 0

SQL> select member from v$logfile;

MEMBER

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

+DATA/SMRPROD/ONLINELOG/group_1.277.1051537329

+FRA1/SMRPROD/ONLINELOG/group_1.296.1051537329

+DATA/SMRPROD/ONLINELOG/group_2.278.1051537329

+FRA1/SMRPROD/ONLINELOG/group_2.297.1051537331

+DATA/SMRPROD/ONLINELOG/group_3.287.1051537877

+FRA1/SMRPROD/ONLINELOG/group_3.298.1051537877

+DATA/SMRPROD/ONLINELOG/group_4.288.1051537877

+FRA1/SMRPROD/ONLINELOG/group_4.299.1051537877

8 rows selected.

SQL> select GROUP#,THREAD#,STATUS from v$log;

GROUP# THREAD# STATUS

---------- ---------- ----------------

1 1 INACTIVE

2 1 CURRENT

3 2 INACTIVE

4 2 CURRENT

SQL>

NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN

--------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ----------------------------------------

SMRPROD SMRPROD1 SMRPROD READ WRITE PRIMARY NO 0

SQL> alter database add logfile thread 1 group 5 ('+DATA','+FRA1') size 250M,group 6 ('+DATA','+FRA1') size 250M,group 7 ('+DATA','+FRA1') size 250M;

Database altered.

SQL> alter database add logfile thread 2 group 8 ('+DATA','+FRA1') size 250M, group 9 ('+DATA','+FRA1') size 250M,group 10 ('+DATA','+FRA1') size 250M;

Database altered.

SQL> select GROUP#,THREAD#,STATUS from v$log;

GROUP# THREAD# STATUS

---------- ---------- ----------------

1 1 INACTIVE

2 1 CURRENT

3 2 INACTIVE

4 2 CURRENT

5 1 UNUSED

6 1 UNUSED

7 1 UNUSED

8 2 UNUSED

9 2 UNUSED

10 2 UNUSED

10 rows selected.

SQL>

NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN

--------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ----------------------------------------

SMRPROD SMRPROD1 SMRPROD READ WRITE PRIMARY NO 0

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> select GROUP#,THREAD#,STATUS from v$log;

GROUP# THREAD# STATUS

---------- ---------- ----------------

2 1 CURRENT

4 2 CURRENT

5 1 UNUSED

6 1 UNUSED

7 1 UNUSED

8 2 UNUSED

9 2 UNUSED

10 2 UNUSED

8 rows selected.

SQL> alter system archive log current;

System altered.

SQL> /

System altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter database drop logfile group 4;

Database altered.

SQL> select GROUP#,THREAD#,STATUS from v$log;

GROUP# THREAD# STATUS

---------- ---------- ----------------

5 1 ACTIVE

6 1 ACTIVE

7 1 CURRENT

8 2 CURRENT

9 2 INACTIVE

10 2 INACTIVE

6 rows selected.

SQL> select member from v$logfile;

MEMBER

+DATA/SMRPROD/ONLINELOG/group_5.290.1051801493

+FRA1/SMRPROD/ONLINELOG/group_5.327.1051801493

+DATA/SMRPROD/ONLINELOG/group_6.291.1051801493

+FRA1/SMRPROD/ONLINELOG/group_6.328.1051801493

+DATA/SMRPROD/ONLINELOG/group_7.292.1051801495

+FRA1/SMRPROD/ONLINELOG/group_7.329.1051801495

+DATA/SMRPROD/ONLINELOG/group_8.293.1051801503

+FRA1/SMRPROD/ONLINELOG/group_8.330.1051801505

+DATA/SMRPROD/ONLINELOG/group_9.294.1051801505

+FRA1/SMRPROD/ONLINELOG/group_9.331.1051801505

+DATA/SMRPROD/ONLINELOG/group_10.295.1051801505

+FRA1/SMRPROD/ONLINELOG/group_10.332.1051801507

12 rows selected.

SQL>

SQL> select BYTES/1024/1024 from v$log;

BYTES/1024/1024

---------------

250

6 rows selected.

SQL>

Oracle Database Commands and Practical Examples

HOME

Monday 24 June 2019

Online Redo log file drop and recreate

No comments:

Post a Comment