Undo Tablespace Management

In an RAC environment, each instance is assigned with its own Undo Tablespace. Only the instance assigned to the undo tablespace can write to it, however all instances can read blocks from all the undo tablespaces.

You have to assign the undo tablespace by specifying a value to the init parameter UNDO_TABLESPACE for each instance. If you do not set this parameter then each instances will use the first available undo tablespace. If the undo tablespace is not available then system rollback segment is used.

You can switch the Undo Tablespace dynamically using the following statement,

SQL> Alter system set undo_tablespace=undotbs03 sid='prod2';

The pending offline tablespace will be unavailable for all other instances until all the transactions against that tablespace is committed.

Thanks

Redo Logfile Management

While creating new redo log groups, take into account the following information,
Thread, Location, Number of Members and Size of the members.

I am adding 3 groups each on instance 1 and instance 2. Two members per group.

Instance 1

SQL> Alter Database Add logfile thread 1 group 1 ('+ASMDB', '+ASMFLASH') Size 100M;
Database altered

SQL> Alter Database Add logfile thread 1 group 2 ('+ASMDB', '+ASMFLASH') Size 100M;
Database Altered

SQL> Alter Database Add logfile thread 1 group 3 ('+ASMDB', '+ASMFLASH') Size 100M;
Database Altered

If thread1 is not enabled enable it. To enable a thread it must have at least two redo log groups.

SQL> Alter Database Enable Public Thread 1;
Database Altered.

Instance 2

SQL> Alter Database Add logfile thread 2 group 4 ('+ASMDB', '+ASMFLASH') Size 100M;
Database Altered

SQL> Alter Database Add logfile thread 2 group 5 ('+ASMDB', '+ASMFLASH') Size 100M;
Database Altered

SQL> Alter Database Add logfile thread 2 group 6 ('+ASMDB', '+ASMFLASH') Size 100M;
Database Altered

If thread 2 is not enabled enable it, To enable a thread it must have atleast two redo log groups.

SQL> Alter Database Enable Public Thread 2;
Database Altered

Note 1: Since you are using thread clause in the alter database statement, you can issue the above statements from any instance.

Note 2: If you omit the thread clause then the redo group will be a part of the instance/thread that issued the statement.

Now we are going to add third member to each group.

SQL> Alter Database add logfile member '+ASMDATA' to group 1;
Database Altered

SQL> Alter Database add logfile member '+ASMDATA' to group 2;
Database Altered

SQL> Alter Database add logfile member '+ASMDATA' to group 3;
Database Altered

SQL> Alter Database add logfile member '+ASMDATA' to group 4;
Database Altered

SQL> Alter Database add logfile member '+ASMDATA' to group 5;
Database Altered

SQL> Alter Database add logfile member '+ASMDATA' to group 6;
Database Altered

Now we are going to resize the redo logfiles

You cannot resize logfiles. To resize a logfile, you have to create a new logfile group with new size and drop the existing old logfile group.

Now we are going to remove the redo logfile members

SQL> Alter Database drop logfile member '+ASMDB/gcprod/onlinelog/group_1.276.703096065';

Note 1: You can drop the redo logfile members if it is not part of active or current group.

Note 2: Make sure the corresponding redo log group is archived.

Now we are going to remove the redo log groups

SQL> Alter database drop logfile group 3;
Database Altered

SQL> Alter database drop logfile group 6;
Database Altered

Note 1: To drop a redo logfile group the instance must have atleast 3 groups.

Note 2: The group must not be the current group. If it is the current group switch the group until it becomes inactive.

SQL> Alter System Switch logfile;

Now we are going to rename the logfile members

Step 1: Shutdown the database

SQL> Shut immediate;

Step 2: Copy the redo log files to the new location using OS commands.

It is straight forward to move the files from one filesystem to another filesystem using the OS mv command. However if you use ASM, it becomes difficult for you to move files between diskgroups as files stored in ASM cannot be accessed using normal OS commands. Inorder to move the files between diskgroup within the ASM you have to use RMAN or use DBMS_FILE_TRANSFER package.

Click here for the steps to move ASM files between diskgroups.

Step 3: Startup the database in Mount state.

$ sqlplus / as sysdba
SQL> Startup Mount;

Step 4: Rename the redo log members

SQL> Alter Database Rename file '+ASMDB/gcprod/onlinelog/group_1.276.703096065' to '+ASMDATA/gcprod/onlinelog/group_1.276.703096065';
Database Altered.

Step 5: Open the database

SQL> Alter Database Open;

Now we are going to clear logfiles

You may need to clear the logfiles in the situation where the current redo logfile becomes corrupted or when there are only two groups and your redolog file becomes corrupted.

SQL> Alter Database clear logfile group 1;

If the corrupt redo logfile is not archived,

SQL> Alter database clear unarchived logfile group 1;

The above statement clears the corrupted redo logfile and avoids archiving them.

Note 1: The cleared redolog file cannot be used for recovery.

Note 2: If the cleared redolog file is needed to bring a tablespace to online from offline, it cannot be done. You have to drop the tablespace and recreate it or perform incomplete recovery.

Now we are going to view information about logfiles

The following views are used for viewing information about logfiles.

V$log, V$logfile, V$log_history.