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.

nohup

When a user logs out of his session, the shell is killed. Hence any background processes started in the shell also gets killed. To avoid this and make background processes run even when the user logs out use nohup command.

When nohup (no hangup) command is prefixed with any command, the command continue to run even when the user who run that command logs out.

$ nohup compress comp.dmp &
$

The & indicates that the command runs in the background.

When you log out the session the parent is killed, in such situation the kernel assigns the orphan (child) to the system's init process (PID 1) the parent of all shells. Hence when the user logs out, init takes over the parentage of any process run with nohup.

If you run more than one command in a pipeline, then use the nohup command at the beginning of each command in the pipeline,

$ nohup grep 'ORA-' alert_prod.log & nohup sort &

Thanks

at jobs

Schedule jobs using 'at' command

at command is used to schedule jobs. It takes a series of commands as input and execute it at a particular time in the future. It differs from cron which is used for recurring executions (every hour, every day etc).

at offers the following keywords to be used for scheduling, now, noon, midnight, today and tomorrow. It also accepts the + operator. You can use hours, days, weeks, months and years keywords along with the + operator. For eg,

at 13
at 2.15pm
at midnight
at now + 1 day
at 3:30 January 26, 1986

To schedule job,

$ at 21:29 /export/rmanbkp.sh
$ at 13:15
at> /data_back/expprod.sh
ctrl-d
$

To view the scheduled jobs,

$ at -l
oracle.1266893940.a Mon Feb 22 21:29:00 GMT 2010
oracle.1269888300.a Mon Mar 29 13:15:00 GMT 2010

To remove a job

$ at -r oracle.1269888300.a

Note: There is no way you can find out the name of the program scheduled to run. Only job number is dislayed.

Thanks