I just ran a status command on a Listener, and this was the output.
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status BLOCKED, has 1 handler(s) for this service...
Service "+ASM_XPT" has 1 instance(s).
Instance "+ASM1", status BLOCKED, has 1 handler(s) for this service...
Service "RACDB" has 2 instance(s).
Instance "RACDB1", status READY, has 3 handler(s) for this service...
Instance "RACDB2", status READY, has 3 handler(s) for this service...
Service "RACDBXDB" has 2 instance(s).
Instance "RACDB1", status READY, has 1 handler(s) for this service...
Instance "RACDB2", status READY, has 1 handler(s) for this service...
Service "RACDB_XPT" has 2 instance(s).
Instance "RACDB1", status READY, has 3 handler(s) for this service...
Instance "RACDB2", status READY, has 3 handler(s) for this service...
What does BLOCKED mean? I can connect to the DB just fine.
These entries refer to ASM instance, not to the database instances. Obviously we use dynamic listener registration (no static SID entries in listener.ora). Unlike a database instance an ASM instance cannot be OPENED, it is in MOUNT status and because it cannot accept connections, the instance has status BLOCKED (while the regular and open database instances have status READY).
In conclusion, Oracle 10g now we have 2 instances, one is database instance like old one, (8i , 9i) and the new one is +ASM instance.... ASM stands for automatic storage management.......... so this might be the reason..........but i am not so sure about this........
Saturday, February 24, 2007
Friday, February 9, 2007
SYSAUX
Prior to Oracle9i Database, if the DEFAULT TABLESPACE was not specified when the user was created, it would default to the SYSTEM tablespace. If the user did not specify a tablespace explicitly while creating a segment, it was created in SYSTEM—provided the user had quota there, either explicitly granted or through the system privilege UNLIMITED TABLESPACE. To solve this problem we may create default tablespace in oracle 9i.
ALTER DATABASE DEFAULT TABLESPACE;
We can verify default tablespace for a database using following command:
SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';
Several schemas such as the intelligent agent user DBSNMP, data mining user ODM are not directly related to user operations, but are important to database integrity nonetheless. Some of these schemas used to have SYSTEM as their default tablespace.
Oracle Database 10g introduces a new tablespace called SYSAUX that holds the objects of these schemas. This tablespace is created automatically during database creation and is locally managed. The only change allowed is the name of the datafile.This approach supports recovery when the corruption of SYSTEM requires a full database recovery. Objects in SYSAUX can be recovered as any normal user object while the database itself remains operational.
ALTER DATABASE DEFAULT TABLESPACE
We can verify default tablespace for a database using following command:
SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';
Several schemas such as the intelligent agent user DBSNMP, data mining user ODM are not directly related to user operations, but are important to database integrity nonetheless. Some of these schemas used to have SYSTEM as their default tablespace.
Oracle Database 10g introduces a new tablespace called SYSAUX that holds the objects of these schemas. This tablespace is created automatically during database creation and is locally managed. The only change allowed is the name of the datafile.This approach supports recovery when the corruption of SYSTEM requires a full database recovery. Objects in SYSAUX can be recovered as any normal user object while the database itself remains operational.
Subscribe to:
Comments (Atom)
