How to calculate the size of Oracle Database

What script do I run to get the size of an Oracle Database?

The following script calculates the storage allocated to an Oracle database, i.e., its overall size. You will need DBA privilege to run this script.


select ROUND( ( a.data_size + b.temp_size +
c.redo_size + d.cf_size +
e.bct_size)
/1024/1024/1024
) "total_GB_size"
from
( select SUM(bytes) data_size from v$datafile) a,
( select NVL(sum(bytes),0) temp_size from v$tempfile) b,
( select SUM(bytes) redo_size from v$log) c,
( select SUM(block_size*file_size_blks) cf_size from v$controlfile) d,
( select NVL(bytes,0) bct_size from v$block_change_tracking) e

select a.data_size+b.temp_size+c.redo_size "total_size"
from ( select sum(bytes)/1024/1024 data_size
from dba_data_files )a,
( select nvl(sum(bytes),0)/1024/1024 temp_size
from dba_temp_files ) b,
( select sum(bytes)/1024/1024 redo_size
from sys.v_$log ) c

Manually configure the Oracle 10g EM dbconsole

Manually configure the Oracle 10g EM dbconsole
Overview
When you choose to create a preconfigured database during the Oracle 10g installation, you can select the Oracle Enterprise Manager (OEM) interface that you want to use to manage the database. The following options are available:

Database Grid Control

This option is available only if an Oracle Management Agent is installed on the system. When the Installer detects an Oracle Management Agent on the system, it allows you to choose this option and specify the Oracle Management Service that you want to use to manage the database.

If an Oracle Management Agent is not installed, you must choose to use Database Control to manage the database. However, if you install Oracle Management Agent after you install Oracle Database, you can then use Grid Control to manage this database.

Database Control
This option is selected by default if an Oracle Management Agent is not installed on the system. However, even if a Management Agent is installed, you can still choose to configure Database Control to manage the database.


Custom installation

If you choose the Custom installation type or the Advanced database configuration option during the installation, the Installer does not display the OEM setup screens. Instead, it runs the Database Configuration Assistant (DBCA) in interactive mode, which enables you to create a custom database.

DBCA also enables you to specify the Oracle Enterprise Manager interface that you want to use. Furthermore, you can also use DBCA after the installation to configure Database Control for a database that was not previously configured to use it.
However, if you decide to setup your own Database, you must install the Database Control manually.



Setup your own Database and manually install the Database Control

The Database Control relies on various underlying technologies to discover, monitor, and administer the Oracle Database environment.
From the Database Control, you can monitor and administer a single Oracle Database instance.

The Database Control Framework consists of the Database Control and its underlying technologies:
A local version of the Oracle Management Service designed to work with the local database or clustered database.
A local Oracle Management Repository installed in the local database and designed to store management data for the Database Control.

The following steps have to be performed.

Create your own Database


Create the Database Control Repository and setup the OC4J Application Server
Make sure, that you can connect to the Repository Database (Test it with SQL*Plus). Examples for Setup Files can be found here:
Windows
Linux

Now start the Oracle EM dbconsole Build Script

Windows Environment

Here Database DBPORD is using listener port number 1526 but OEM uses listener port number 1521.


d:\PROD\ADMIN\SCRIPTS\com>emca -repos create

STARTED EMCA at Oct 10, 2008 5:34:08 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: DBPORD
Listener port number: 1526
Password for SYS user:
Password for SYSMAN user:

Do you wish to continue? [yes(Y)/no(N)]: y
Oct 10, 2008 5:34:59 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at D:\oracle\product\10.2.0\db_1\cfgtoollog
s\emca\DBPORD\emca_2008-10-10_05-34-08-PM.log.
Oct 10, 2008 5:35:01 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Oct 10, 2008 5:36:50 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Enterprise Manager configuration completed successfully
FINISHED EMCA at Oct 10, 2008 5:36:50 PM




D:\PROD\ADMIN\SCRIPTS\COM>emca -config dbcontrol db

STARTED EMCA at Oct 10, 2008 5:43:43 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: DBPORD
Listener port number: 1526
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ D:\oracle\product\10.2.0\db_1

Database hostname ................ lhopis01.myCompany.ca
Listener port number ................ 1526
Database SID ................ DBPORD
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: y
Oct 10, 2008 5:45:04 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at D:\oracle\product\10.2.0\db_1\cfgtoollog
s\emca\DBPORD\emca_2008-10-10_05-43-43-PM.log.
Oct 10, 2008 5:45:21 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Oct 10, 2008 5:46:01 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Oct 10, 2008 5:46:01 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is http://lhopis01.lh.myCompany.ca:1158/em
<<<<<<<<<<<
Enterprise Manager configuration completed successfully
FINISHED EMCA at Oct 10, 2008 5:46:01 PM



Automatically start and stop the DB-Console

D:\PROD\ADMIN\SCRIPTS\COM> emctl start dbconsole
D:\PROD\ADMIN\SCRIPTS\COM> emctl stop dbconsole
D:\PROD\ADMIN\SCRIPTS\COM> emctl status dbconsole

Troubleshooting

Check \oracle\product\10.2.0\db_1\sysman\config
Check listener is up and running

All Control file lost Recover database from RMAN Backup (ORA-00205: error in identifying control file)

All Control file lost Recover database from RMAN Backup (ORA-00205: error in identifying control file)

During media failure (single disk crash) the online redo log is usually available so it is possible to recover up to the point of failure. Quite often ,Windows Oracle instance can be started in nomount mode (i.e. spfile is also available).

In this scenario all Control files are lost and it’s not multiplexed. In that case you have no option but to use a backup control file. The recovery needs to be performed from within RMAN, and requires that all logs (archived and current online logs) since the last backup are available. The logs are required because all datafiles must also be restored from backup. The database will then have to be recovered up to the time the control files went missing. This can only be done if all intervening logs are available.

In this example RMAN is not using catalog.

Try to start database and found following error.

d:> sqlplus sys/**** as sysdba;

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Sep 29 11:05:12 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to an idle instance.

ORCL: SQL> startup;
ORACLE instance started.

Total System Global Area 524288000 bytes
Fixed Size 1291576 bytes
Variable Size 306186952 bytes
Database Buffers 213909504 bytes
Redo Buffers 2899968 bytes
ORA-00205: error in identifying control file, check alert log for more info


In Alter log file I found following error:

Tue Sep 29 11:05:18 2009
ALTER DATABASE MOUNT
Tue Sep 29 11:05:18 2009
ORA-00202: control file: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


Complete Recovery by last night RMAN BACKUP set

1. d:\> rman target /

Recovery Manager: Release 10.2.0.3.0 - Production on Tue Sep 29 10:19:36 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ORCL (not mounted)



2. RMAN> set dbid=1096578125

executing command: SET DBID

****set DBID - get this from the name of the controlfile autobackup.
For example, if autobackup name is CTL_SP_BAK_C-1507972899-20050124-00 the the DBID is 1507972899. This step will not be required if the instance is



3. RMAN> set controlfile autobackup format for device type disk to 'D:\ABC\BACKUP\RMAN\ORCL\cf_%d_%F';

executing command: SET CONTROLFILE AUTOBACKUP FORMAT
using target database control file instead of recovery catalog

**** Verify the format of your backup set



4. RMAN> restore controlfile from autobackup;

Starting restore at 29-SEP-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

recovery area destination: D:\oracle\product\10.2.0\flash_recovery_area
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20090929
channel ORA_DISK_1: looking for autobackup on day: 20090928
channel ORA_DISK_1: autobackup found: D:\ABC\BACKUP\RMAN\ORCL\cf_ORCL_c-10965
78125-20090928-03
channel ORA_DISK_1: control file restore from autobackup complete
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02.CTL
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL03.CTL
Finished restore at 29-SEP-09

*** Now that control files have been restored, the instance can mount the


5. RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1


6. RMAN> restore database;

Starting restore at 29-SEP-09
Starting implicit crosscheck backup at 29-SEP-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 7 objects
Finished implicit crosscheck backup at 29-SEP-09

Starting implicit crosscheck copy at 29-SEP-09
using channel ORA_DISK_1
Finished implicit crosscheck copy at 29-SEP-09

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_28\O1_MF_1_48_5D1QDPFJ_.ARC
File Name: D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_28\O1_MF_1_49_5D21CNOB_.ARC
File Name: D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_28\O1_MF_1_50_5D21CPS7_.ARC
File Name: D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_29\O1_MF_1_51_5D38N85L_.ARC
File Name: D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_29\O1_MF_1_52_5D45J1YK_.ARC

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
restoring datafile 00002 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
restoring datafile 00003 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
restoring datafile 00004 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
restoring datafile 00005 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_01.DBF
restoring datafile 00006 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_02.DBF
restoring datafile 00007 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS1_01.DBF
restoring datafile 00008 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS1_02.DBF
restoring datafile 00009 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS2_01.DBF
restoring datafile 00010 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS2_02.DBF
restoring datafile 00011 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS3_01.DBF
restoring datafile 00012 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS3_02.DBF
restoring datafile 00013 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS4_01.DBF
restoring datafile 00014 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS4_02.DBF
restoring datafile 00015 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS5_01.DBF
restoring datafile 00016 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS5_02.DBF
restoring datafile 00017 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS6_01.DBF
restoring datafile 00018 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS6_02.DBF
restoring datafile 00019 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_S_01.DBF
restoring datafile 00020 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_S_02.DBF
restoring datafile 00021 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_SI_01.DBF
restoring datafile 00022 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_SI_02.DBF
restoring datafile 00023 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_HL7_01.DBF

restoring datafile 00024 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_HL7_02.DBF

restoring datafile 00025 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_HL7I_01.DB
F
restoring datafile 00026 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_HL7I_02.DB
F
restoring datafile 00027 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_AUD_01.DBF

restoring datafile 00028 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_AUD_02.DBF

restoring datafile 00029 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_AUDI_01.DB
F
restoring datafile 00030 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_AUDI_02.DB
F
restoring datafile 00031 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_RHAPSODY_0
1.DBF
restoring datafile 00032 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_RHAPSODY_0
2.DBF
restoring datafile 00033 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_OPIS_RPT_0
1.DBF
restoring datafile 00034 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_OPIS_RPT_0
2.DBF
channel ORA_DISK_1: reading from backup piece D:\ABC\BACKUP\RMAN\ORCL\DB_ORCL
_T698759044_S731_P1
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\ABC\BACKUP\RMAN\ORCL\DB_ORCL_T698759044_S731_P1 tag=FULL BACK
UP
channel ORA_DISK_1: restore complete, elapsed time: 00:01:39
Finished restore at 29-SEP-09

** Database must be recovered because all datafiles have been restored from backup




7. RMAN> recover database;

Starting recover at 29-SEP-09
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 48 is already on disk as file D:\ORACLE\PRODUCT\10
.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_28\O1_MF_1_48_5D1QDPFJ_.ARC
archive log thread 1 sequence 49 is already on disk as file D:\ORACLE\PRODUCT\10
.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_28\O1_MF_1_49_5D21CNOB_.ARC
archive log thread 1 sequence 50 is already on disk as file D:\ORACLE\PRODUCT\10
.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_28\O1_MF_1_50_5D21CPS7_.ARC
archive log thread 1 sequence 51 is already on disk as file D:\ORACLE\PRODUCT\10
.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_29\O1_MF_1_51_5D38N85L_.ARC
archive log thread 1 sequence 52 is already on disk as file D:\ORACLE\PRODUCT\10
.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_29\O1_MF_1_52_5D45J1YK_.ARC
archive log thread 1 sequence 53 is already on disk as file D:\ORACLE\PRODUCT\10
.2.0\ORADATA\ORCL\REDO02.LOG
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=46
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=47
channel ORA_DISK_1: reading from backup piece D:\ABC\BACKUP\RMAN\ORCL\ARCH_P
OM_1_T698759096_S734_P1
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\ABC\BACKUP\RMAN\ORCL\ARCH_ORCL_1_T698759096_S734_P1 tag=TAG20
090928T114456
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG
\2009_09_29\O1_MF_1_46_5D475JO8_.ARC thread=1 sequence=46
channel default: deleting archive log(s)
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG
\2009_09_29\O1_MF_1_46_5D475JO8_.ARC recid=641 stamp=698841715
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG
\2009_09_29\O1_MF_1_47_5D475K17_.ARC thread=1 sequence=47
channel default: deleting archive log(s)
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG
\2009_09_29\O1_MF_1_47_5D475K17_.ARC recid=640 stamp=698841713
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG
\2009_09_28\O1_MF_1_48_5D1QDPFJ_.ARC thread=1 sequence=48
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG
\2009_09_28\O1_MF_1_49_5D21CNOB_.ARC thread=1 sequence=49
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG
\2009_09_28\O1_MF_1_50_5D21CPS7_.ARC thread=1 sequence=50
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG
\2009_09_29\O1_MF_1_51_5D38N85L_.ARC thread=1 sequence=51
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG
\2009_09_29\O1_MF_1_52_5D45J1YK_.ARC thread=1 sequence=52
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG thread=1 se
quence=53
media recovery complete, elapsed time: 00:00:18
Finished recover at 29-SEP-09



8. RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 09/29/2009 10:42:28
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

-- Recovery completed. The database must be opened with RESETLOGS
-- because a backup control file was used. Can also use
-- "alter database open resetlogs" instead.




9. RMAN> alter database open resetlogs;

database opened




**** Several points are worth emphasizing.*****
1. Recovery using a backup controlfile should be done only if a current control file is
unavailable.
2. All datafiles must be restored from backup. This means the database will need to be
recovered using archived and online redo logs. These MUST be available for recovery until the time of failure.

3. As with any database recovery involving RESETLOGS, take a fresh backup immediately.

4. Technically the above is an example of complete recovery - since all committed
transactions were recovered. However, some references consider this to be incomplete
recovery because the database log sequence had to be reset.
After recovery using a backup controlfile, all temporary files associated with locallymanaged tablespaces are no longer available. You can check that this is so by querying the view V$TEMPFILE - no rows will be returned. Therefore tempfiles must be added (or recreated) before the database is made available for general use. In the case at hand, the tempfile already exists so we merely add it to the temporary tablespace. This can be done using SQLPlus or any tool of your choice:

SQL> alter tablespace temp add tempfile
'D:\oracle_data\datafiles\ORCL\TEMP01.DBF';
Tablespace altered.

RMAN-00554: initialization of internal recovery manager package failed/RMAN-04005: error from target database:/ORA-01031: insufficient privileges

Platform: Oracle 10.2.0.3.0 on Windows 2003 server

*** Logged onto Windows 2003 server Local Windows user which is a member of Administrator and Ora_DBA group. Same database user is created into database and which is authenticated by OS.

Problem:
When try to connect RMAN for database backup following error happen.

Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.

d:\OPIS2005\ADMIN\SCRIPTS\com>set oracle_sid=POM

d:\OPIS2005\ADMIN\SCRIPTS\com>rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Jul 28 15:05:33 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

RMAN-00571: =================================================
RMAN-00569: =========== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: =================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-01031: insufficient privileges


Logged on to the box with a local user that is a member of both thelocal Administrators group and the local ora_dba group.Connection to db with sqlplus, using local credentials:

d:\OPIS2005\ADMIN\SCRIPTS\com>sqlplus /

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jul 28 15:06:51 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Release 10.2.0.3.0 - Production

POM: SQL>


But connecting to rman requires me to spell it out ...d:\OPIS2005\ADMIN\SCRIPTS\com>sqlplus /

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jul 28 15:06:51 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Release 10.2.0.3.0 - Production

POM: SQL>
Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.

d:\OPIS2005\ADMIN\SCRIPTS\COM>rman target sys/manager1@lom.lhsc.world

Recovery Manager: Release 10.2.0.3.0 - Production on Tue Jul 28 15:10:13 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: LOM (DBID=2640997403)

RMAN>

*** Local Windows user is a member of Administrator and Ora_DBA group. Same database user is created into database and which is authenticated by OS.

Tried to connect as SYSDBA:
d:\opis2005\ADMIN\SCRIPTS\COM ( POM )sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jul 28 15:23:28 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Release 10.2.0.3.0 - Production

POM: SQL>

So, things are correctly set up for connecting with local sysdbacredentials

SOLUTION:

We found 10g oem agent installed on same box in a different HOME.

The box had two Oracle homes on it, one for a single 10g datbase and one for the 10g oem agent. In the PATH environment variable, the agent home preceded the db home.

Example: C:\OracleHomes\agent10g\jlib;C:\OracleHomes\agent10g\bin;D:\oracle\product\10.2.0\db_1\bin;C:\Program Files\HP\NCU;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;C:\Program Files\OmniBack\bin\

Flipping that -- putting the db home first -- solved the problem and allowedd:\OPIS2005\ADMIN\SCRIPTS\com>rman target /' to connect as it is supposed to.

Why to use /3GB Switch in Windows Oracle

Why to use /3GB Switch in Windows Oracle

Windows 32-bit servers have a problem due to the 32 bit limitation of 2 to the 32nd power (2**32 ~= 2 gigabytes), where Oracle cannot use "above the line" RAM resources.

The 3GB switch only give you an additional two gig, while AWE can go higher


How to Set the /3GB Startup Switch in Windows

Windows Server 2003 includes support for a startup switch that lets you tune the allocation of use of memory and memory address space. Regardless of the amount of physical memory in your system, Windows uses a virtual address space of 4 GB, with 2 GB allocated to user-mode processes (for example, applications) and 2 GB allocated to kernel-mode processes (for example, the operating system and kernel-mode drivers). On systems that have 1 GB or more of physical memory, these two startup switches can be used to allocate more memory to applications (3 GB) and less memory to the operating system (1 GB). This additional virtual address space helps reduce the amount of memory fragmentation in the virtual address space of the Exchange information store process.

Procedure
To Set the /3GB Startup Switch in Windows Server 2003

1. Right-click My Computer and select Properties. The System Properties dialog box will appear.
2. Click the Advanced tab.
3. In the Startup and Recovery area, click Settings. The Startup and Recovery dialog box will appear.
4. In the System startup area, click Edit. This will open the Windows boot.ini file in Notepad.
5. In the [Operating Systems] section, add the following switches to the end of the startup line that includes the /fastdetect switch: /3GB
6. Save the changes and close Notepad.
7. Click OK two times to close the open dialog boxes, and then restart the computer for the change to take effect.

Uninstalling Oracle 10g Manually from Windows XP/Windows 2003 Server

Uninstalling Oracle 10g Manually from Windows XP/Windows 2003 Server

I have uninstalled Oracle 10g manually for my Windows XP and Windows 2003 server. Here are the steps bellow:

Step 1:
Use Oracle Universal installer to deinstall Oracle.
Follow the steps and select correct oracle home to remove it.

Step 2:

After running the supplied Oracle uninstallation utility (which may or may not do some or all of the following):
• Stop any Oracle services that have been left running.
Start->Settings->Control Panel->Services
Look for any services with names starting with 'Oracle' and stop them.

Step 3:
Take the backup of your registry in case you accidentally delete other thing (wrongly delete any entry in registry might let to reinstall whole server.)

In DOS command prompt type regedit
Move the icon in my computer then file-export

Step 4:
• Run regedit and delete the following keys (some may have slightly different names in your registry):
HKEY_CURRENT_USER\SOFTWARE\ORACLE
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\Application\Oracle.oracle
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\OracleDBConsole
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Oracle10g_home
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\OraclService

Step 5:

Reboot the system.

• Delete the Oracle home directory
C:\Oracle
• Delete the Oracle Program Files directory:
C:\Program Files\Oracle
• Delete the Oracle Start Menu shortcuts directory:
C:\Documents and Settings\All Users\Start Menu\Programs\Oracle*
Where * indicates the name of your install. Look for and remove all Oracle directories from that location.

Step 6:
• Remove Oracle refereces from the path. To edit your path go to:

Start->Settings->Control Panel->System->Advanced->Environment Variables

Edit both of the environment variables user PATH and system PATH. Remove any Oracle references in them.

• Remove Oracle.DataAccess and any Polic.Oracle files from the GAC which is at:
C:\Windows\assembly\


Now you can install Oracle 10g.

How to release dead lock in Oracle

1. Find out who is locking in Deadlock scenario

SQL> select (select username from v$session where sid=a.sid) blocker,
a.sid, ' is blocking ',
(select username from v$session where sid=b.sid) blockee,
b.sid
from v$lock a, v$lock b
where a.block = 1 and b.request > 0
and a.id1 = b.id1 and a.id2 = b.id2

/BLOCKER SID 'ISBLOCKING' BLOCKEE SID------------------------------ ----------ORAUSER 94 is blocking OPIS 1032.

2. Find out SID and Serial#

SQL> SELECT s.sid, s.serial#, s.osuser,FROM v$session swhere s.sid=94SQL> SID SERIAL# OSUSER PROGRAM---------- ---------- -------------------------------94 39897 mross3.

Kill the session:

SQL> alter system kill session '94,39897';

System altered.