The DROP temporary tablespace command takes long time and in fact it hangs because of the entries left in v$sort_usage.
Create a new temporary tablespace.
CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE '/oradata/TEMP2_01.dbf' SIZE 1000M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 32M;
Enable autoextend for this temporary tablespace.
ALTER DATABASE TEMPFILE '/oradata/TEMP2_01.dbf' AUTOEXTEND ON NEXT 100M;
Assign this tablespace for all users as default temporary tablespace.
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;
Reset
the database connections on App servers OR kill all the inactive
sessions corresponding to the entries in v$sort_usage. You can find out
those sessions by
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;
Drop the previous temporary tablespace.
DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
Thursday, July 30, 2009
Sunday, August 29, 2004
OpenLDAP - Oracle Names Resolution
Unfortunately, Oracle doesn’t provide specific
instructions for using any LDAP server other than their Oracle
Internet Directory which is too large and complex for our simple
needs. Further, Oracle also stated that Oracle Names Server is being
deprecated in favor of LDAP. These instructions will help to configure
OpenLDAP for Oracle Names Resolution.
Requirements
:
- RedHat Linux 9 installed and operational including network
- RPMS installed from RedHat CDs
openldap-2.0.27-8
openldap-servers-2.0.27-8
openldap-clients-2.0.27-8
slapd.conf
Configure slapd.conf similar to the following:
include
/etc/openldap/schema/core.schema
include
/etc/openldap/schema/cosine.schema
include
/etc/openldap/schema/inetorgperson.schema
include
/etc/openldap/schema/nis.schema
include
/etc/openldap/schema/redhat/rfc822-MailMember.schema
include
/etc/openldap/schema/redhat/autofs.schema
include
/etc/openldap/schema/redhat/kerberosobject.schema
include
/etc/openldap/schema/oidbase.schema
include
/etc/openldap/schema/oidnet.schema
include
/etc/openldap/schema/oidrdbms.schema
database
ldbm
suffix
"dc=example, dc=com"
rootdn
"dc=example, dc=com"
rootpw
{SSHA}fZyQ6cZiBwXU9bmU2u+pSqvNt/ok5UfW
directory
/var/lib/ldap
index
objectClass,uid,uidNumber,gidNumber,memberUid eq
index
cn,mail,surname,givenname eq,subinitial
This
configuration includes oidbase.schema,
oidnet.schema and oidrdbms.schema,
which include attribute definitions needed by our directory. These
files can be grabbed from $ORACLE_HOME/ldap/admin directory, but we
need to convert them to OpenLDAP format. The converted files are
provided at the end under schemas. Copy them to /etc/openldap/schema
directory. The password for rootpw is generated with slappasswd
command.
Start
OpenLDAP Server :
Change
directory to /etc/openldap
#
slapd –f slapd.conf --- You cannot stop after starting
#
chown ldap.ldap slapd.conf
#/etc/rc.d/init.d/ldap
start
Creating the Directory layout
Notice
that we already defined the base DN using the suffix keyword in slapd.conf:
suffix
"dc=example, dc=com"
We
need to create an actual entry for this in the LDAP directory itself
using an LDIF-formatted file and ldapadd. Create a file named directory.ldif
containing:
dn:
dc=example, dc=com
objectClass:
top
objectClass:
dcObject
objectClass:
organization
dc:
example
o:
Example, Inc.
Be
sure to not leave any trailing spaces, as they may confuse ldapadd. Import LDIF entries into the directory using:
#
ldapadd –x –h localhost –D 'dc=example,dc=com' –f
directory.ldif –W
This
will prompt for LDAP password. Type your rootdn password.
Next,
create OracleContext in the directory under dc=example,dc=com to serve as
a container for net service entries. Import the following LDIF
entries using the above ldapadd command after creating a file named
orclcontext.ldif containing:
dn:
cn=OracleContext,dc=example,dc=com
objectclass:
orclContext
cn:
OracleContext
Now
add net service entries. Create a file named netservice.ldif
containing the following entries and import them using ldapadd
command. A typical LDIF entry will look something like this for each
database.
dn:
cn=orcl,cn=OracleContext,dc=example,dc=com
objectclass:
top
objectclass:
orclNetService
cn:
orcl
orclNetDescString:
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1521)))(CONNECT_DATA=(SID=ORCL)))
To
delete from LDAP :
# ldapdelete
-x -h localhost -D 'dc=example,dc=com'
'cn=orcl,cn=OracleContext,dc=example,dc=com' -W
Client
Configuration :
You
will also need to configure the client to use LDAP in its resolution
order. Oracle client version 8.1.6 and above can use LDAP directly
for lookups. If you have any clients lower than 8.1.6, you'll have to
use the Oracle Names LDAP Proxy.
sqlnet.ora
NAMES.DEFAULT_DOMAIN=example.com
NAMES.DIRECTORY_PATH=(LDAP,
TNSNAMES)
ldap.ora
DEFAULT_ADMIN_CONTEXT="dc=example,dc=com"
DIRECTORY_SERVERS=ldap-server:port
DIRECTORY_SERVER_TYPE=ad
Schemas :
$ cat oidbase.schema
attributetype
( 2.16.840.1.113894.7.1.1 NAME 'orclVersion'
EQUALITY
caseIgnoreMatch
SYNTAX
'1.3.6.1.4.1.1466.115.121.1.15'
SINGLE-VALUE
)
attributetype
( 2.16.840.1.113894.7.1.2 NAME 'orclOracleHome'
SYNTAX
'1.3.6.1.4.1.1466.115.121.1.15'
SINGLE-VALUE
)
attributetype
( 2.16.840.1.113894.7.1.3 NAME 'orclSystemName'
EQUALITY
caseIgnoreMatch
SYNTAX
'1.3.6.1.4.1.1466.115.121.1.15'
SINGLE-VALUE
)
attributetype
( 2.16.840.1.113894.7.1.4 NAME 'orclServiceType'
EQUALITY
caseIgnoreMatch
SYNTAX
'1.3.6.1.4.1.1466.115.121.1.15'
SINGLE-VALUE
)
attributetype
( 2.16.840.1.113894.7.1.5 NAME 'orclSid'
EQUALITY
caseIgnoreMatch
SYNTAX
'1.3.6.1.4.1.1466.115.121.1.15'
SINGLE-VALUE
)
attributetype
( 2.16.840.1.113894.7.1.6 NAME 'orclProductVersion'
EQUALITY
caseIgnoreMatch
SYNTAX
'1.3.6.1.4.1.1466.115.121.1.15' )
objectClass
( 2.16.840.1.113894.7.2.2 NAME 'orclContainer' SUP 'top'
STRUCTURAL
MUST
( cn ) )
objectClass
( 2.16.840.1.113894.7.2.3 NAME 'orclContext' SUP 'top' STRUCTURAL
MUST
( cn ) )
objectClass
( 2.16.840.1.113894.7.2.6 NAME 'orclSchemaVersion' SUP 'top'
STRUCTURAL
MUST
( cn $ orclProductVersion ) )
attributetype
( 2.16.840.1.113894.3.1.12 NAME 'orclNetDescName'
EQUALITY
distinguishedNameMatch
SYNTAX
'1.3.6.1.4.1.1466.115.121.1.12'
SINGLE-VALUE
)
attributetype
( 2.16.840.1.113894.3.1.13 NAME 'orclNetDescString'
EQUALITY
caseIgnoreMatch SYNTAX '1.3.6.1.4.1.1466.115.121.1.15'
SINGLE-VALUE
)
objectClass
( 2.16.840.1.113894.7.2.1001 NAME 'orclService' SUP 'top'
STRUCTURAL
MUST
( cn )
MAY
( orclServiceType $ orclOracleHome $ orclSystemName $ orclSid $
orclNetDescName $ orclNetDescString $ orclVersion $ Description ) )
$ cat
oidnet.schema
attributetype
( 2.16.840.1.113894.3.1.1 NAME 'orclNetSourceRoute'
SYNTAX
'1.3.6.1.4.1.1466.115.121.1.5'
SINGLE-VALUE
)
attributetype
( 2.16.840.1.113894.3.1.2 NAME 'orclNetLoadBalance'
SYNTAX
'1.3.6.1.4.1.1466.115.121.1.5'
SINGLE-VALUE
)
attributetype
( 2.16.840.1.113894.3.1.3 NAME 'orclNetFailover'
SYNTAX
'1.3.6.1.4.1.1466.115.121.1.5'
SINGLE-VALUE
)
attributetype
( 2.16.840.1.113894.3.1.4 NAME 'orclNetSdu'
EQUALITY
caseIgnoreMatch
SYNTAX
'1.3.6.1.4.1.1466.115.121.1.15'
SINGLE-VALUE
)
attributetype
( 2.16.840.1.113894.3.1.5 NAME 'orclNetServer'
EQUALITY
caseIgnoreMatch
SYNTAX
'1.3.6.1.4.1.1466.115.121.1.15'
SINGLE-VALUE
)
attributetype
( 2.16.840.1.113894.3.1.6 NAME 'orclNetServiceName'
EQUALITY
caseIgnoreMatch
SYNTAX
'1.3.6.1.4.1.1466.115.121.1.15'
SINGLE-VALUE
)
attributetype
( 2.16.840.1.113894.3.1.7 NAME 'orclNetInstanceName'
EQUALITY
caseIgnoreMatch
SYNTAX
'1.3.6.1.4.1.1466.115.121.1.15'
SINGLE-VALUE
)
attributetype
( 2.16.840.1.113894.3.1.8 NAME 'orclNetHandlerName'
EQUALITY
caseIgnoreMatch
SYNTAX
'1.3.6.1.4.1.1466.115.121.1.15'
SINGLE-VALUE
)
attributetype
( 2.16.840.1.113894.3.1.9 NAME 'orclNetParamList'
EQUALITY
caseIgnoreMatch
SYNTAX
'1.3.6.1.4.1.1466.115.121.1.15' )
attributetype
( 2.16.840.1.113894.3.1.10 NAME 'orclNetAuthenticationType'
EQUALITY
caseIgnoreMatch
SYNTAX
'1.3.6.1.4.1.1466.115.121.1.15'
SINGLE-VALUE
)
attributetype
( 2.16.840.1.113894.3.1.11 NAME 'orclNetAuthParams'
EQUALITY
caseIgnoreMatch
SYNTAX
'1.3.6.1.4.1.1466.115.121.1.15'
SINGLE-VALUE
)
attributetype
( 2.16.840.1.113894.3.1.14 NAME 'orclNetAddressString'
EQUALITY
caseIgnoreMatch SYNTAX '1.3.6.1.4.1.1466.115.121.1.15'
SINGLE-VALUE
)
attributetype
( 2.16.840.1.113894.3.1.15 NAME 'orclNetProtocol'
EQUALITY
caseIgnoreMatch
SYNTAX
'1.3.6.1.4.1.1466.115.121.1.15'
SINGLE-VALUE
)
attributetype
( 2.16.840.1.113894.3.1.16 NAME 'orclNetShared'
EQUALITY
caseIgnoreMatch
SYNTAX
'1.3.6.1.4.1.1466.115.121.1.15'
SINGLE-VALUE
)
attributetype
( 2.16.840.1.113894.3.1.17 NAME 'orclNetAddrList'
EQUALITY
caseIgnoreMatch
SYNTAX
'1.3.6.1.4.1.1466.115.121.1.15' )
attributetype
( 2.16.840.1.113894.3.1.18 NAME 'orclNetProtocolStack'
EQUALITY
caseIgnoreMatch
SYNTAX
'1.3.6.1.4.1.1466.115.121.1.15'
SINGLE-VALUE
)
attributetype
( 2.16.840.1.113894.3.1.19 NAME 'orclNetDescList'
EQUALITY
caseIgnoreMatch
SYNTAX
'1.3.6.1.4.1.1466.115.121.1.15' )
attributetype
( 2.16.840.1.113894.3.1.20 NAME 'orclNetConnParamList'
EQUALITY
caseIgnoreMatch
SYNTAX
'1.3.6.1.4.1.1466.115.121.1.15' )
attributetype
( 2.16.840.1.113894.3.1.21 NAME 'orclNetAuthenticationService'
EQUALITY
caseIgnoreMatch
SYNTAX
'1.3.6.1.4.1.1466.115.121.1.15'
SINGLE-VALUE
)
objectClass
( 2.16.840.1.113894.3.2.5 NAME 'orclNetService' SUP 'top'
STRUCTURAL
MUST
( cn )
MAY
( orclNetDescName $ orclNetDescString $ orclVersion $ Description )
)
objectClass
( 2.16.840.1.113894.3.2.4 NAME 'orclNetDescriptionList' SUP 'top'
STRUCTURAL
MUST
( cn )
MAY
( orclNetDescList $ orclNetSourceRoute $ orclNetLoadBalance $
orclNetFailover $ orclNetShared $ orclVersion $ Description ) )
objectClass
( 2.16.840.1.113894.3.2.3 NAME 'orclNetDescription' SUP 'top'
STRUCTURAL
MUST
( cn )
MAY
( orclNetAddrList $ orclNetProtocolStack $ orclNetSdu $ orclSid $
orclNetServer $ orclNetServiceName $ orclNetInstanceName $
orclNetHandlerName $ orclOracleHome $ orclNetAuthenticationType $
orclNetAuthenticationService $ orclNetAuthParams $ orclNetParamList $
orclNetConnParamList $ orclNetSourceRoute $ orclNetLoadBalance $
orclNetFailover $ orclNetShared $ orclVersion $ Description ) )
objectClass
( 2.16.840.1.113894.3.2.2 NAME 'orclNetAddressList' SUP 'top'
STRUCTURAL
MUST
( cn )
MAY
( orclNetAddrList $ orclNetSourceRoute $ orclNetLoadBalance $
orclNetFailover $ orclNetShared $ orclVersion $ Description ) )
objectClass
( 2.16.840.1.113894.3.2.1 NAME 'orclNetAddress' SUP 'top'
STRUCTURAL
MUST
( cn )
MAY
( orclNetAddressString $ orclNetProtocol $ orclNetShared $
orclVersion $ Description ) )
$ cat
oidrdbms.schema
attributetype
( 2.16.840.1.113894.2.1.1 NAME 'orclDBtrustedUser'
EQUALITY
distinguishedNameMatch
SYNTAX
'1.3.6.1.4.1.1466.115.121.1.12' )
attributetype
( 2.16.840.1.113894.2.1.2 NAME 'orclDBServerMember'
EQUALITY
distinguishedNameMatch
SYNTAX
'1.3.6.1.4.1.1466.115.121.1.12' )
attributetype
( 2.16.840.1.113894.2.1.3 NAME 'orclDBEntUser'
EQUALITY
distinguishedNameMatch
SYNTAX
'1.3.6.1.4.1.1466.115.121.1.12' )
attributetype
( 2.16.840.1.113894.2.1.4 NAME 'orclDBEntRoleAssigned'
EQUALITY
distinguishedNameMatch
SYNTAX
'1.3.6.1.4.1.1466.115.121.1.12' )
attributetype
( 2.16.840.1.113894.2.1.5 NAME 'orclDBServerRole'
EQUALITY
caseIgnoreMatch
SYNTAX
'1.3.6.1.4.1.1466.115.121.1.15' )
attributetype
( 2.16.840.1.113894.2.1.6 NAME 'orclDBTrustedDomain'
EQUALITY
caseIgnoreMatch SYNTAX '1.3.6.1.4.1.1466.115.121.1.15'
SINGLE-VALUE
)
attributetype
( 2.16.840.1.113894.2.1.7 NAME 'orclDBRoleOccupant'
EQUALITY
distinguishedNameMatch
SYNTAX
'1.3.6.1.4.1.1466.115.121.1.12' )
attributetype
( 2.16.840.1.113894.2.1.8 NAME 'orclDBDistinguishedName'
EQUALITY
distinguishedNameMatch SYNTAX '1.3.6.1.4.1.1466.115.121.1.12'
SINGLE-VALUE
)
attributetype
( 2.16.840.1.113894.2.1.9 NAME 'orclDBNativeUser'
EQUALITY
caseIgnoreMatch
SYNTAX
'1.3.6.1.4.1.1466.115.121.1.15'
SINGLE-VALUE
)
attributetype
( 2.16.840.1.113894.2.1.10 NAME 'orclDBGlobalName'
EQUALITY
caseIgnoreMatch
SYNTAX
'1.3.6.1.4.1.1466.115.121.1.15'
SINGLE-VALUE
)
objectClass
( 2.16.840.1.113894.2.2.1 NAME 'orclDBServer' SUP 'orclService'
STRUCTURAL
MAY
( userCertificate $ orclDBtrustedUser $ orclDBGlobalName ) )
objectClass
( 2.16.840.1.113894.2.2.2 NAME 'orclDBEnterpriseDomain' SUP top
STRUCTURAL
MUST
cn
MAY
( orclDBServerMember $ orclDBEntUser $ orclDBTrustedDomain ) )
objectClass
( 2.16.840.1.113894.2.2.3 NAME 'orclDBEnterpriseRole' SUP top
STRUCTURAL
MUST
cn
MAY
( orclDBServerRole $ orclDBEntRoleAssigned $ description $ seeAlso $
o $ ou $ orclDBRoleOccupant ) )
objectClass
( 2.16.840.1.113894.2.2.4 NAME 'orclDBEntryLevelMapping' SUP top
STRUCTURAL
MUST
cn
MAY
( orclDBDistinguishedName $ orclDBNativeUser ) )
objectClass
( 2.16.840.1.113894.2.2.5 NAME 'orclDBSubtreeLevelMapping' SUP top
STRUCTURAL
MUST
cn
MAY
( orclDBDistinguishedName $ orclDBNativeUser ) )
Subscribe to:
Posts (Atom)