How to recreate a undo tablespace

There was an issue in the undo tablespace , where the size of one of the datafiles in the undo tablespace(ASM) did not match the size in the control file and I did the folowing to recreate the undo tablespace:

1. Make sure the database was last cleanly shut down.

sqlplus /nolog
SQL>connect sys/change@db as sysdba
SQL> shutdown immediate

2. mount database in RESTRICT mode, using pfile.

SQL> STARTUP RESTRICT MOUNT pfile=C:\Oracle\db\initdb.ora
ORACLE instance started. Total System Global Area 1620126452 bytes
Fixed Size 457460 bytes
Variable Size 545259520 bytes
Database Buffers 1073741824 bytes
Redo Buffers 667648 bytes
Database mounted.

3. Try to offline drop the bad datafile.

SQL> ALTER DATABASE DATAFILE 'C:\ORADATA\DB\UNDOTBS2_02.DBF' OFFLINE DROP;

*
ERROR at line 1:
ORA-01548: active rollback segment ‘_SYSSMU11$’ found, terminate dropping
tablespace

or this SQL:

DROP TABLESPACE undotbs2 INCLUDING CONTENTS AND DATAFILES ;
*
ERROR at line 1:
ORA-01548: active rollback segment ‘_SYSSMU11$’ found, terminate dropping
tablespace

4. Use this query to see how many rollback segments were corrupted:

SQL>select segment_name,status,tablespace_name from dba_rollback_segs where status='NEEDS RECOVERY';
SEGMENT_NAME STATUS TABLESPACE_NAME
—————————— —————- —————–
_SYSSMU11$ NEEDS RECOVERY UNDOTBS2
_SYSSMU12$ NEEDS RECOVERY UNDOTBS2
_SYSSMU13$ NEEDS RECOVERY UNDOTBS2
_SYSSMU14$ NEEDS RECOVERY UNDOTBS2
_SYSSMU15$ NEEDS RECOVERY UNDOTBS2
_SYSSMU16$ NEEDS RECOVERY UNDOTBS2
_SYSSMU17$ NEEDS RECOVERY UNDOTBS2
_SYSSMU18$ NEEDS RECOVERY UNDOTBS2
_SYSSMU19$ NEEDS RECOVERY UNDOTBS2
_SYSSMU20$ NEEDS RECOVERY UNDOTBS2

5. Add the following line to pfile:

_corrupted_rollback_segments =('_SYSSMU11$','_SYSSMU12$','_SYSSMU13$','_SYSSMU14$','_SYSSMU15$','_SYSSMU16$','_SYSSMU17$','_SYSSMU18$','_SYSSMU19$','_SYSSMU20$')

Make sure you uncomment “undo_management=AUTO”, and specify you want to use UNDOTBS1 as undo tablespace.

#undo_management=AUTO
undo_tablespace=UNDOTBS1

6. Start the database again:

SQL> STARTUP RESTRICT MOUNT pfile=C:\Oracle\db\initdb.ora


7. Drop bad rollback segments


SQL> drop rollback segment "_SYSSMU11$";
Rollback segment dropped.
…

SQL> drop rollback segment "_SYSSMU20$";
Rollback segment dropped.

8. Check again

SQL> select segment_name,status,tablespace_name from dba_rollback_segs;

SEGMENT_NAME STATUS TABLESPACE_NAME
—————————— —————- —————
SYSTEM ONLINE SYSTEM
_SYSSMU2$ ONLINE UNDOTBS1
_SYSSMU3$ ONLINE UNDOTBS1
_SYSSMU4$ ONLINE UNDOTBS1
_SYSSMU5$ ONLINE UNDOTBS1
_SYSSMU6$ ONLINE UNDOTBS1
_SYSSMU7$ ONLINE UNDOTBS1
_SYSSMU8$ ONLINE UNDOTBS1
_SYSSMU9$ ONLINE UNDOTBS1
_SYSSMU10$ ONLINE UNDOTBS1
_SYSSMU21$ ONLINE UNDOTBS1

9. Now drop bad undo TABLESPACE UNDOTBS2;

SQL> drop TABLESPACE UNDOTBS2;

10. Recreate the undo rollback tablespace with all its rollback segments

SQL>CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE 'C:\oradata\DB\UNDOTBS01.DBF' SIZE 2000M reuse AUTOEXTEND ON ;


11. Change undo tablespace

ALTER SYSTEM SET undo_tablespace = UNDOTBS1 ;

12. Remove the following line from pfile

_corrupted_rollback_segments =('_SYSSMU11$','_SYSSMU12$','_SYSSMU13$','_SYSSMU14$','_SYSSMU15$','_SYSSMU16 $','_SYSSMU17$','_SYSSMU18$','_SYSSMU19$','_SYSSMU20$')

and uncomment “undo_management=AUTO”

undo_management=AUTO
undo_retention=10800
undo_tablespace=UNDOTBS1

13. Shutdown database

SQL>shutdown immediate;

14. Edit initCRM_18.ora, make sure you change ‘undo_tablespace=UNDOTBS2″ to “undo_tablespace=UNDOTBS1″, then start oracle database:


sqlplus /nolog
SQL>connect sys/change@db as sysdba
SQL> STARTUP RESTRICT MOUNT pfile=C:\Oracle\db\initdb.ora
ORACLE instance started.
Total System Global Area 1620126452 bytes
Fixed Size 457460 bytes
Variable Size 545259520 bytes
Database Buffers 1073741824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.

15. Create Undo tablespace:

SQL>CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE 'C:\oradata\db\UNDOTBS02.DBF' SIZE 2000M reuse AUTOEXTEND ON ;
SQL>DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES ;

16. Startup database with spfile

SQL> startup;
ORACLE instance started.
Total System Global Area 1620126452 bytes
Fixed Size 457460 bytes
Variable Size 545259520 bytes
Database Buffers 1073741824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.

Oracle 10g CLONE FROM RAC TO NON-RAC (RMAN)

Database

Target database – PRD10G => This is the source database in RACRecovery Catalog – RMCAT10G (in Machard) => This is the catalog database for RMANAuxillary database(New DB) – TST => This is the target database Non-RAC
Note: Backups are in tape , MML is Netbackup

Steps

1. The first step would be to create a auxillary instance, which will be in no-mount state.To prepare a instance, create a pfile from the spfile of PRD10G.
Eg. Create pfile=’/tmp/initprd.ora’ from spfile;

2. Then edit this pfile to remove all the parameters denoting the Cluster, in our case I commented it as below;
#tst1.__db_cache_size=260046848#tst2.__db_cache_size=260046848....#*.cluster_database=true#*.remote_listener='LISTENERS_tst'*.background_dump_dest='/rdbms/v10.1/admin/tst/bdump'*.compatible='10.1.0.4.0'*.control_files='+DG4_DEV/tst/controlfile/backup.277.629493875','+DG_DEV/tst/controlfile/backup.264.629493875'*.core_dump_dest='/rdbms/v10.1/admin/tst/cdump'*.user_dump_dest='/rdbms/v10.1/admin/tst/udump'*.db_block_size=8192*.db_create_file_dest='+DG4_DEV' ===> Modify it accoring to the file system or ASM diskgroup in the destination server....*.sessions=170*.sga_target=471859200*.undo_management='AUTO'undo_tablespace='UNDOTBS1'
3. Create a password file to login remote to the auxillay instance.

4. Add entries to the Listener.ora file and also add the prd and rmcat10g entries to the tnsnames.ora if they are not available.

5. Create any directories specified in the pfile eg.bdump,udump etc.

6. Use this pfile to startup the new instance in nomount mode
Startup new instance to check if all parameters are correct and for further RMAN operations
SQL> show parameter cluster_database
NAME TYPE VALUE------------------------------------ ----------- ------------------------------cluster_database boolean FALSEcluster_database_instances integer 1
SQL> show parameter thread
NAME TYPE VALUE------------------------------------ ----------- ------------------------------thread integer 0

7. Check the location of the target database datafiles so that we can specify the new names for them, in our case:
SQL> select name from v$datafile;
NAME--------------------------------------------------------------------------------+DG4/prd/datafile/system.260.629141721+DG4/prd/datafile/undotbs1.276.629141721...+DG4/prd/datafile/usermgr_admin.256.629142617
12 rows selected.

8. Connect to RMAN with – PRD as target, RMCAT10G as catalog and TST as Auxiliary instances.

9. From the catalog RC_% Views find the time or SCN that you want to duplicate, In our case it is up to a specific time.

10. Then run a run block similar to this:
run{allocate auxiliary channel c1 device type sbt PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';allocate auxiliary channel c2 device type sbt PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
duplicate target database to 'tst' db_file_name_convert=('+DG4/prd/datafile/','+DG4_DEV/tst/datafile/')logfilegroup 1('+DG4_DEV/tst/onlinelog/redo01.log') size 100M,group 2 ('+DG4_DEV/tst/onlinelog/redo02.log') size 100MUNTIL TIME "to_date('2007-08-08:07:00:00','YYYY-MM-DD:HH24:MI:SS')" ;
release channel c1;release channel c2;}

This will clone a database called tst from prd as a non-rac database.

Happy cloning.

Enabling Trace in Oracle

Tracing a session is a imporatant phase of a problem analysis for a query,job in Oracle.There are various methods of enabling trace for sessions.Following are a few methods for enabling trace.
To check if any trace is enabled in the current database, the following query can be used:
select * from DBA_ENABLED_TRACES ;

1. Enable trace at instance level
Start trace:
SQLPLUS> ALTER SYSTEM SET sql_trace = TRUE;
Stop trace:
SQLPLUS> ALTER SYSTEM SET sql_trace = FALSE;

2. Enable trace for the current session
Start trace:
ALTER SESSION SET sql_trace = TRUE; (or)EXECUTE dbms_session.set_sql_trace (TRUE); (or)EXECUTE dbms_support.start_trace;Stop trace:
ALTER SESSION SET sql_trace = FALSE; (or)EXECUTE dbms_session.set_sql_trace (FALSE); (or)EXECUTE dbms_support.stop_trace;

3. Enable trace in another session
Find out SID and SERIAL# from v$session. For example:
Start trace:
EXECUTE DBMS_MONITOR..start_trace_in_session (SID, SERIAL#);
With Waits and Binds
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(SID, SERIAL#, waits=>TRUE);
With Waits
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(SID, SERIAL#, waits=>TRUE, binds=>TRUE);

Stop trace:
EXECUTE dbms_support.stop_trace_in_session (SID, SERIAL#);

Oracle 10g - RMAN Block change tracking

There are performance issues associated with incremental backups where most of the times we have issues with the CPU/IO being consumed more. In Oracle 10g it is possible to track changed blocks using a change tracking file.

Prior to introduction of Oracle 10g block change tracking (BCT), RMAN had to scan the whole datafile to and filter out the blocks that were not changed since base incremental backup and overhead or incremental backup was as high as full backup. Oracle 10g new feature, block change tracking, minimizes number of blocks RMAN needs to read to a strict minimum. With block change tracking enabled RMAN accesses on disk only blocks that were changed since the latest base incremental backup.

Enabling change tracking does produce a small overhead, but it greatly improves the performance of incremental backups.

The current change tracking status can be displayed using the following query:
SELECT status FROM v$block_change_tracking;

Change tracking is enabled using the ALTER DATABASE command:
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/ts1/oradata/block_change_track.dbf';

The tracking file is created with a minumum size of 10M and grows in 10M increments. It's size is typically 1/30,000 the size of the datablocks to be tracked.

Change tracking can be disabled using the following command:
ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

Renaming or moving a tracking file can be accomplished in the normal way using the ALTER DATABASE RENAME FILE command. If the instance cannot be restarted you can simply disable and re-enable change tracking to create a new file. This method does result in the loss of any current change information.

Background Process – Change Tracking Writer (CTWR). This process takes care of logging information about changed blocks in block change tracking file.

Increasing the Speed of Export

Exporting data is a common day to day activity done by most of the DBA's, and when it comes to speeding up the Export jobs these are few tips on it :

• Use Direct Path – Direct path exports (DIRECT=Y) allow the export utility to skip the SQL evaluation buffer, whereas the conventional path export executes SQL SELECT statements. With direct path, the data is read from disk into the buffer cache, returning rows directly to the export client. This can offer substantial performance gains, depending on the actual data. When using the direct path, the recordlength parameter should also be used to optimize performance.

• Use Subsets – By subsetting the data using the QUERY option, the export process is only executed against the data that needs to be exported. If tables have old rows that are never updated, the old data should be exported once, and from that point only the newer data subsets should be exported. Subsets cannot be specified with direct path exports since SQL is necessary to create the subset.

Note: Use a par file for the query as it can help reduce a lot of formatting on the command.

• Use a Larger Buffer – For conventional path exports, a larger buffer will increase the number of rows that are processed between each physical write to the export file. Fewer physical writes equals greater performance. The following formula can be used to determine a proper buffer size:
buffer size = rows in array * max row size

• Separate Tables – Separate those tables that require consistent=y from those that don’t, in order to expedite the export. This way, the performance penalty will only be incurred for those tables that actually require it.
For the table with one million rows, the following benchmark tests were performed using the different export options.

• Indexes=No - This will reduce the time taken to export the indexes which is worth creating after the import.

• Set a higher value for the recordlength parameter - Specifies the length of the file record in bytes. This parameter affects the amount of data that accumulates before it is written to disk. The highest value is 64KB.

Backing up OCR and Voting Disk

Backup OCR
============

There are a couple of methods to backup the OCR.

Note: Oracle automatically backs up the OCR every 4 hours.


BAckup can be done using the ocrconfig tool to add a backup location.

ocrconfig -backuploc

Note:This command has to be run as root



A logical backup can be taken using the ocrconfig tool.

ocrconfig -export

a logical backup can only be imported using the ocrconfig tool.

ocrconfig -import



A OCR mirror location can be specified for Oracle 10GR2 using ocrconfig.

ocrconfig -replace ocrmirror



BAckup Voting disk
===================

When raw devices are used for the voting disks, backup on UNIX platform can be taken by dd command


dd if=/dev/rdsk/vot1 of=$ORACLE_HOME/votebackup/

ASM - Adding a new diskgroup

ASM new disk groups can be created easily and the steps below can be followed to achieve the same:

1.Check the available disks for adding to the diskgroup with header status candidate
Log on to the ASM instance and check the header status of the disks to see which are the disks available for ASM.The Candidate disks can be used to create a new diskgroup or for adding it to a existing diskgroup.

Query: select HEADER_STATUS,MODE_STATUS,NAME,PATH,TOTAL_MB,FREE_MB from v$asm_disk;

Sample Output:
MOUNT_S HEADER_STATU MODE_ST NAME PATH TOTAL_MB FREE_MB------- ------------ ------- ------------------ ------------------------- ---------- ----------CLOSED CANDIDATE ONLINE /dev/raw/raw35 548437 0CLOSED CANDIDATE ONLINE /dev/raw/raw36 548437 0CLOSED CANDIDATE ONLINE /dev/raw/raw37 548437 0CLOSED CANDIDATE ONLINE /dev/raw/raw38 548437 0CLOSED CANDIDATE ONLINE /dev/raw/raw39 548437 0CLOSED CANDIDATE ONLINE /dev/raw/raw40 548437

2. Find the current diskgroup names

Query:select name,total_mb from v$asm_dikgroup;

3. Create a new diskgroup with the new name- (There will be no rebalancing here)
Creating a new diskgroup will not have rebalancing operation.There is an option for redundancy at the ASM level, in the example I have redundancy at the hardware level hence the diskgroup has a clause for external redundancy.

Query: CREATE DISKGROUP DG9 EXTERNAL REDUNDANCY disk '/dev/raw/raw35','/dev/raw/raw36','/dev/raw/raw37','/dev/raw/raw38','/dev/raw/raw39','/dev/raw/raw40';

4. Other instances will not mount the new diskgroup hence add an entry in the spfile , so they will be monted from the next startup and manually mount them for this first time.

Query : ALTER DISKGROUP DG9 MOUNT;