Cleaning up a failed Oracle 11g grid infrastructure install.

No Comments

Unfortunately, the installer or more specifically the root.sh script for the Oracle 11g grid (Clusterware) installer can be very flaky.

 

If it fails and you need to fix something or rerun it for any reason, it will fail the next time if you have not cleaned up the install by de-configuring CRS. I also like to wipe the installation off altogether and restart from a clean base. Here are the steps.

/u01/app/11.2.0/grid/crs/install/rootcrs.pl -deconfig -verbose -force

/u01/app/11.2.0/grid/crs/install/rootcrs.pl -deconfig -verbose -force -lastnode

At this point, you could rerun root.sh (after you have fixed the problem, but if you have closed the installer or just want to restart from a clean base then continue with the steps below.

 

Get the oracle home from the inventory then delete them both

 cat /etc/oraInst.loc

cd /u01/app/oraInventory/ContentsXML/

cat inventory.xml

Find the Oracle home

Remove it on all nodes

 rm -R /u01/app/11.2.0/

Also remove the inventory

rm -R /u01/app/oraInventory/

rm -R /etc/oracle

rm /etc/oraInst.loc

rm /etc/oratab

rm /usr/local/bin/dbhome

rm /usr/local/bin/oraenv

rm /usr/local/bin/coraenv

Then change the ownership of the /u01/app directory:

chown oracle:dba /u01/app

 

You could stop there but if you really want to wipe the slate, you could delete your ASM disks  and recreate them fresh before the next install.

 

Delete your ASM disks on node 1

 oracleasm deletedisk DISK1

oracleasm deletedisk DISK2

oracleasm deletedisk DISK3

oracleasm deletedisk DISK4

.

.

.

 

On all nodes

oracleasm scandisks

/usr/sbin/oracleasm exit

 

Now give your node a reboot and you should have a clean base from which to start another install.

 

Manually upgrading to 11g

No Comments
You can directly upgrade an Oracle database from version 9.2.0.4 or higher, directly to 11g

Upgrade Process.

Open database in Upgrade mode:

     startup upgrade

Pre-Upgrade information tool:

     utlu111i.sql
  • Precursor to the upgrade.
  • Generated report on required  and recommended changes to make..
  • Generally increasing tablespace sizes or remove parameters.

Upgrade script:

     catupgrd.sql
  • Makes actual changes to the database.
  • If it is stopped or failes, it can be rerun.
  • Shuts down database on completion.
Restart the database in normal mode.

Upgrade status script:

     utlu111s.sql
  • Verifies that all components have been successfully upgraded.
  • If any components have failed, rerun the catupgrd.sql script.

Post Upgrade actions script:

     catuppst.sql
  • New in 11g.
  • Performs upgrade actions that don’t require upgrade mode.
     utlrp.sql
  • Can be ran at the same time as catuppst.sql.
  • It recompiles INVALID objects.
catdwgrd.sql would carry out the downgrade to the previous version if you needed it.

The Database Upgrade Assistant (DBUA)

Can upgrade database and ASM instances simultaniously.
Faster at the end as it uses parallel compilation on multi-CPU systems
Allows you to upgrade from XE to 11g
You can move datafiles around as part of the upgrade.
The DBUA asks you to supply the ORACLE_BASE parameter which it uses to derive default db locations as well as the DIAGNOSTIC_DEST parameter.
If you specify AUTOEXTEND on the command line, Oracle will allow tablespaces to AUTOEXTEND then sets them back to their original settings after the upgrade.

The compatible parameter

The compatible parameter controls whether a lot of functionality is available.
The default value is: 11.1.0 or 11.2.0 and the minimum allowed value is 10.0.0
Categories: Oracle Tasks, Tutorial Tags: Tags:

Resizing the Temp tablespace in Oracle

No Comments

In much the same way as with the Undo tablespace, you may want to reduce the size of the Temp tablespace in Oracle to reclaim space.

 

11g

—-
ALTER TABLESPACE [tbsp_name] SHRINK SPACE KEEP 30M;

10g and earlier

——————

Unfortunatly, if the temp tablespace is full (as will be the case if it has been expanding) you will not be able to reduce he size directly. Also, restarting the database will not clear out the data.

The process or doing this is similar to that of reducing the size of the undo tablespace.

——

1. First, find the location of the temp tablespace datafile:

select * from dba_temp_files;

If there is more than one, you can check which one is the default with the following query:

select * from database_properties
where property_name like ‘%TABLESPACE’;

2. Next, generate a script to create the existing temp tablespace:

SELECT DBMS_METADATA.GET_DDL(‘TABLESPACE’, ‘TEMP’) from dual;

i.e.
CREATE TEMPORARY TABLESPACE “TEMP” TEMPFILE
‘/TESTDB/oradata/TESTDB/temp01.dbf’ SIZE 104857600
AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576

save this.

3. Use that script to create a new temporary tablespace. Alter the tablespace anme, file name and size as appropriate.

You only need to create a small file which you will be deleting.

CREATE TEMPORARY TABLESPACE “TEMP2” TEMPFILE ‘/TESTDB/oradata/TESTDB/temp02.dbf’ SIZE 50M AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576;

4. Make the new temporary tablespace the default:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE “[Tablespace Name]”;

5. Make sure that the new tablespace is now the default:

select * from database_properties
where property_name like ‘%TABLESPACE’;

*** If Database version is < 9i see below ***

6. Drop the old tablespace:

DROP tablespace [Tablespace Name] including contents and datafiles;

7. Re-create the old tablespace using the script from step 2 – changing the size as appropriate.

8. Set the re created tablespace to be the default

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE “[Tablespace Name]”;

9. Make sure that the correct tablespace is now the default:

select * from database_properties
where property_name like ‘%TABLESPACE’;

10. Drop the other tablespace:

DROP tablespace [Tablespace Name] including contents and datafiles;

If you are using a database version below 9i then you will need to do this step before step 6:

Use the below query to identify any residual dead connections still alloctaed to the first TEMP tablespace. Dropping the TEMP tablespace (Step 7) will not complete until these dead session dissapear or are killed. The dead connections can be killed using the ALTER SYSTEM KILL command with the sid and Serial# returned from the query.

SELECT b.tablespace
, b.segfile#
, b.segblk#
, b.blocks
, a.sid
, a.serial#
, a.username
, a.osuser
, a.status
FROM v$session a
, v$sort_usage b
WHERE a.saddr = b.session_addr

Categories: Oracle Tasks Tags: Tags: ,

How to Reduce the Size of the Undo Tablespace in Oracle

No Comments

There is no function in Oracle (up to 11g currently) that will allow you to reduce the size of the undo tablespace.

Sometimes you will need to free up space and the best way to get it will be to reduce the size of the undo tablespace.

The way to do this is basically to create a new undo tablespace, drop the old one and recreate it with less space.

Here are the exact steps:

1. First, create a script to recreate the undo tablespace exactly as it was (you will need to tweak it to use less space)

In sqlplus:

You can find the location of the undo tablespace datafile with:

select * from dba_data_files;

set long 20000 (to make sure that the output isnt truncated.)

Create the SQL to recreate the tablespace later:

SELECT DBMS_METADATA.GET_DDL(‘TABLESPACE’, ‘UNDOTBS1’) from dual;

2. Create a second undo tablespace to use temporarily while you remove the first.

CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE ‘[FILELOCATION/FILENAME’ SIZE 50 M AUTOEXTEND OFF;

3. You need to set the new Undo tablespace as default otherwise you wont be able to delete the first;

ALTER SYSTEM SET undo_tablespace=UNDOTBS2;

4. Make sure that the default has been set correctly:

SHOW PARAMETER undo_tablespace;

5. Now drop the original tablespace:

DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

6. Use you generated SQL to create the new undo tablespace. Make sure that you change the script to set the size correctly.

7. Next, you need to set the new tablespace as the default:

ALTER SYSTEM SET undo_tablespace=UNDOTBS1;

8. Make sure that the new tablespace is now the default:

SHOW PARAMETER undo_tablespace;

9. Drop the second undo tablespace.

DROP TABLESPACE undotbs2 INCLUDING CONTENTS AND DATAFILES;

Categories: Featured, Oracle Tasks Tags: Tags: ,